java
程序员文章站
2022-03-27 08:05:15
...
package com.zcj.poi;
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;
import org.junit.Test;
public class PoiDemo001 {
@Test
public void test() throws Exception{
Workbook wb = new HSSFWorkbook();//创建excel文件
Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
String safeName = WorkbookUtil.createSafeSheetName("[zhou'sheet2*?]");//创建安全的工作簿名
Sheet sheet2 = wb.createSheet(safeName);
FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
wb.write(execl);//把内容写入流
execl.close();
}
@Test
public void testCells() throws Exception{
Workbook wb = new HSSFWorkbook();//创建excel文件
CreationHelper creationHelper = wb.getCreationHelper();
Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
Row row = sheet1.createRow(0);//定位行
Cell cell = row.createCell(0);//定位单元格
cell.setCellValue("cell");
row.createCell(1).setCellValue(9.9);
row.createCell(2).setCellValue(creationHelper.createRichTextString("This is a String"));
row.createCell(3).setCellValue(true);
String safeName = WorkbookUtil.createSafeSheetName("[zhou'sheet2*?]");//创建安全的工作簿名
Sheet sheet2 = wb.createSheet(safeName);
FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
wb.write(execl);//把内容写入流
execl.close();
}
@Test
public void testCreatingDateCells() throws Exception{
Workbook wb = new HSSFWorkbook();//创建excel文件
CreationHelper creationHelper = wb.getCreationHelper();
CellStyle style = wb.createCellStyle();//创建样式
style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));//设置时间样式
Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
Row row = sheet1.createRow(0);//定位行
Cell cell = null;
cell = row.createCell(0);
cell.setCellValue(new Date());//不使用样式
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(style);//使用样式
cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());//使用日历
cell.setCellStyle(style);
FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
wb.write(execl);//把内容写入流
execl.close();
}
@Test
public void testWorkingWithDifferentTypesOfCells() throws Exception{
Workbook wb = new HSSFWorkbook();//创建excel文件
CreationHelper creationHelper = wb.getCreationHelper();
CellStyle style = wb.createCellStyle();//创建样式
style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));//设置时间样式
Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
Row row = sheet1.createRow(0);//定位行
row.createCell(0).setCellValue(true);
row.createCell(1).setCellValue(Calendar.getInstance());
row.createCell(2).setCellValue(new Date());
row.createCell(3).setCellValue(9.9);
row.createCell(4).setCellValue(creationHelper.createRichTextString("This is a String"));
row.createCell(5).setCellValue("String");
row.createCell(6).setCellType(CellType.ERROR);
FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
wb.write(execl);//把内容写入流
execl.close();
}
@Test
public void testDemonstratesVariousAlignmentOptions() throws Exception{
Workbook wb = new HSSFWorkbook();//创建excel文件
Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
Row row = sheet1.createRow(0);//定位行
row.setHeightInPoints(30);
createCell(wb, row, 0, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
createCell(wb, row, 1, HorizontalAlignment.LEFT, VerticalAlignment.CENTER);
createCell(wb, row, 2, HorizontalAlignment.LEFT, VerticalAlignment.BOTTOM);
createCell(wb, row, 3, HorizontalAlignment.CENTER, VerticalAlignment.TOP);
createCell(wb, row, 4, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
createCell(wb, row, 5, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
createCell(wb, row, 7, HorizontalAlignment.RIGHT, VerticalAlignment.CENTER);
createCell(wb, row, 8, HorizontalAlignment.RIGHT, VerticalAlignment.BOTTOM);
FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
wb.write(execl);//把内容写入流
execl.close();
}
/**
* 设置单元格水平垂直对象方向
* @param wb excel
* @param row 行
* @param column 列
* @param halign 水平方向
* @param valign 垂直方向
*/
private static void createCell(Workbook wb,Row row,Integer column,HorizontalAlignment halign,VerticalAlignment valign){
Cell cell = row.createCell(column);
cell.setCellValue("Align It");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cell.setCellStyle(cellStyle);
}
@Test
public void testWorkingWithBorders() throws Exception{
Workbook wb = new HSSFWorkbook();//创建excel文件
CreationHelper creationHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();//创建样式
//设置边框线条样式
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.DOUBLE);
cellStyle.setBorderRight(BorderStyle.DOUBLE);
//设置边框线条颜色
cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());
cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
Row row = sheet1.createRow(1);//定位行
Cell cell = row.createCell(1);
cell.setCellValue("边框");
cell.setCellStyle(cellStyle);
FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
wb.write(execl);//把内容写入流
execl.close();
}
@Test
public void tests() throws Exception{
Workbook wb = new HSSFWorkbook();//创建excel文件
CreationHelper creationHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();//创建样式
//设置边框线条样式
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.DOUBLE);
cellStyle.setBorderRight(BorderStyle.DOUBLE);
//设置边框线条颜色
cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());
cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
Sheet sheet1 = wb.createSheet("sheet1");//创建工作簿
Row row = sheet1.createRow(1);//定位行
Cell cell = row.createCell(1);
cell.setCellValue("边框");
cell.setCellStyle(cellStyle);
FileOutputStream execl = new FileOutputStream("workbook.xls");//创建一个文件流
wb.write(execl);//把内容写入流
execl.close();
}
@Test
public void testss() throws Exception{
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short) 1);
// Aqua background
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.BIG_SPOTS);
Cell cell = row.createCell((short) 1);
cell.setCellValue("X");
cell.setCellStyle(style);
// Orange "foreground", foreground being the fill foreground not the font color.
style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 2);
cell.setCellValue("Y");
cell.setCellStyle(style);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
}
}