POI的基本应用

用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();

       }

    }

}