用POI可以生成excel文件,基本用法较简单,使用如下:
public class App {
public static void main(String[] args) {
try {
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = wb.createSheet("first sheet");
wb.createSheet("second sheet");
//创建row
HSSFRow row = sheet.createRow(0);
//创建单元格并设置单元格值
row.createCell(0).setCellValue(false);// boolean
row.createCell(1).setCellValue(Calendar.getInstance());//日历
row.createCell(2).setCellValue(new Date());//date
row.createCell(3).setCellValue(123456789.9876543);//double
//string
row.createCell(4).setCellValue(new HSSFRichTextString("文本"));
//对数据进行格式化显示
HSSFCell cell = row.getCell(1);
//通过workbook创建数据格式对象
HSSFDataFormat format = wb.createDataFormat();
//通过workbook创建单元格样式对象
HSSFCellStyle style = wb.createCellStyle();
//设置数据格式
style.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss"));
//设置单元格的样式对象
cell.setCellStyle(style);
row.getCell(2).setCellStyle(style);
style = wb.createCellStyle();
style.setDataFormat(format.getFormat("#,###.0000"));
row.getCell(3).setCellStyle(style);
//设置列宽 1 = 1/20 ()
sheet.setColumnWidth(1, 6000);
sheet.autoSizeColumn((short)2);//设置自动列宽
//操纵文本
row = sheet.createRow(1);
row.createCell(0).setCellValue(new HSSFRichTextString("左下"));
row.createCell(1).setCellValue(new HSSFRichTextString("中中"));
row.createCell(2).setCellValue(new HSSFRichTextString("右上"));
//设置文本的对齐方式
cell = row.getCell(0);
style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//左对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);//下对齐
cell.setCellStyle(style);
cell = row.getCell(1);
style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//下对齐
cell.setCellStyle(style);
cell = row.getCell(2);
style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//左对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);//下对齐
cell.setCellStyle(style);
row.setHeightInPoints(50);//设置行高
//设置文本旋转
cell = row.getCell(0);
style = cell.getCellStyle();
style.setRotation((short)30);
//设置字体和颜色
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeight((short)300);
font.setColor(HSSFColor.RED.index);
style.setFont(font);
//设置文本回绕
String str =
"dddddddddsdfasfdoiuretjijrtlwjertlkwej;lkouifdiudugsfd" ;
cell = row.createCell(3);
cell.setCellValue(new HSSFRichTextString(str));
style = wb.createCellStyle();
style.setWrapText(true);
cell.setCellStyle(style);
//设置边框的特效
row = sheet.createRow(2);
cell = row.createCell(0);
style = wb.createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
style.setTopBorderColor(HSSFColor.GREEN.index);
cell.setCellStyle(style);
cell = row.createCell(1);
style = wb.createCellStyle();
style.setRightBorderColor(HSSFColor.RED.index);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM_DASH_DOT_DOT);
FileOutputStream fos = new FileOutputStream("d:/pio.xls");
cell.setCellStyle(style);
//创建计算列
row = sheet.createRow(3);
row.createCell(0).setCellValue(13);
row.createCell(1).setCellValue(15);
row.createCell(2).setCellValue(17);
cell = row.createCell(3);
cell.setCellFormula("average(A4:C4)");
//移动行
//sheet.shiftRows(1, 3, 2);
//拆分窗口
//1000:x轴拆分距离 2000:y轴拆分距离 2:右侧窗格从第几列开始显示
// 3:下端窗格从第几行开始显示 1:激活的窗格
//sheet.createSplitPane(1000, 2000, 2, 3, 1);
//冻结窗口
//1:列的冻结数量 2:行的冻结数量 3:右侧窗格从第几列开始显示
//4:下端窗格从第几行开始显示
sheet.createFreezePane(1, 2, 3, 4);
wb.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}