java 操作excel文档 使用poi

  1 package kite.poi;
  2 
  3 import java.awt.Font;
  4 import java.io.File;
  5 import java.io.FileOutputStream;
  6 import java.util.Calendar;
  7 import java.util.Date;
  8 
  9 import org.apache.poi.hssf.usermodel.HSSFCell;
 10 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 11 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
 12 import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
 13 import org.apache.poi.hssf.usermodel.HSSFFont;
 14 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
 15 import org.apache.poi.hssf.usermodel.HSSFRow;
 16 import org.apache.poi.hssf.usermodel.HSSFSheet;
 17 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 18 import org.apache.poi.hssf.util.HSSFColor;
 19 import org.apache.poi.ss.usermodel.Cell;
 20 import org.apache.poi.ss.usermodel.RichTextString;
 21 
 22 public class App
 23 {
 24     public static void main(String[] args) throws Exception
 25     {
 26         //创建工作簿
 27         HSSFWorkbook wb = new HSSFWorkbook();
 28         //创建一页
 29         HSSFSheet sheet = wb.createSheet("first sheet");
 30         //创建一行
 31         HSSFRow row = sheet.createRow(0);
 32         //创建列
 33         HSSFCell cell = row.createCell(0);
 34         cell.setCellValue(false);
 35         row.createCell(1).setCellValue(Calendar.getInstance());//日历
 36         row.createCell(2).setCellValue(new Date());
 37         row.createCell(3).setCellValue(123456789.987654321f);
 38         
 39         RichTextString rt = new HSSFRichTextString("dasssssssssssssssssssssssssssssssssssssssssss" +
 40                 "dasssssssssssssssssssssssssssssssssssssssssssss");//富有文本
 41         row.createCell(4).setCellValue(rt);
 42         row.createCell(5).setCellValue("收入多少啊");
 43         
 44         
 45         //格式化数据
 46         HSSFDataFormat format = wb.createDataFormat();//创建格式化对象
 47         HSSFCellStyle style = wb.createCellStyle();//创建单元格样式
 48         
 49         //设置格式
 50         style.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss"));
 51         cell = row.getCell(1);//对日历进行格式化
 52         cell.setCellStyle(style);
 53         //设置列宽
 54         sheet.setColumnWidth(1, 5000);//单位:1/20
 55         sheet.autoSizeColumn(2);
 56         
 57         //数字格式化
 58         style = wb.createCellStyle();
 59         style.setDataFormat(format.getFormat("#,####.000"));
 60         row.getCell(3).setCellStyle(style);
 61         
 62         //文本自动换行
 63         sheet.setColumnWidth(4, 5000);
 64         style = wb.createCellStyle();
 65         style.setWrapText(true);//回绕文本
 66         row.getCell(4).setCellStyle(style);//设置样式到 富文本
 67         
 68         //设置文本对齐方式
 69         sheet.setColumnWidth(0 , 5000);
 70         row = sheet.createRow(1);
 71         row.createCell(0).setCellValue("左上");
 72         row.createCell(1).setCellValue("中中");
 73         row.createCell(2).setCellValue("右下");
 74         
 75         //对齐方式 --左上
 76         style = wb.createCellStyle();
 77         style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//左对齐
 78         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);//上对齐
 79         row.getCell(0).setCellStyle(style);
 80         
 81         //对齐方式--中中
 82         style = wb.createCellStyle();
 83         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 84         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
 85         row.getCell(1).setCellStyle(style);
 86         
 87         //对齐方式--右下
 88         style = wb.createCellStyle();
 89         style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
 90         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
 91         row.getCell(2).setCellStyle(style);
 92         
 93         //设置行高      像素
 94         row.setHeightInPoints(50);
 95         
 96         //设置字体
 97         style = row.getCell(1).getCellStyle();  //对当前行的第一列设置样式
 98         HSSFFont font = wb.createFont();
 99         font.setColor(HSSFColor.RED.index);
100         font.setFontName("宋体");
101         font.setFontHeightInPoints((short)18);
102         style.setFont(font);
103         
104         //文本旋转
105         style.setRotation((short)30);
106         
107         //设置边框
108         row = sheet.createRow(2);
109         cell = row.createCell(0);
110         style = wb.createCellStyle();
111         style.setBorderBottom(HSSFCellStyle.BORDER_DASH_DOT_DOT);
112         style.setTopBorderColor(HSSFColor.RED.index);
113         cell.setCellStyle(style);
114         
115         //计算列
116         row = sheet.createRow(3);
117         row.createCell(0).setCellValue(11.5f);
118         row.createCell(1).setCellValue(25.6f);
119         row.createCell(2).setCellValue(50);
120         row.createCell(3).setCellFormula("sum(A4:C4)");
121         
122         //整体移动行
123         sheet.shiftRows(1, 3, 2);
124         
125         //拆分窗格
126         //1000:左侧窗格的宽度
127         //2000:上侧窗格的高度
128         //3:右侧窗格开始显示的列的索引
129         //4:下侧窗格开始显示的行的索引
130         //1:激活的哪个面板区
131         sheet.createSplitPane(1000, 2000, 3, 4, 1);
132         
133         //冻结窗口
134         sheet.createFreezePane(1, 2, 3, 4);
135         
136         wb.write(new FileOutputStream(new File("F:\\excel.xls")));
137         
138     }
139 }