POI Excel 导入导出重点

1 package test.worksheet;
2
3 import java.io.File;
4 import java.io.FileOutputStream;
5 import java.io.IOException;
6 import java.io.OutputStream;
7 import java.util.Date;
8
9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
10 import org.apache.poi.hssf.util.HSSFColor;
11 import org.apache.poi.ss.usermodel.Cell;
12 import org.apache.poi.ss.usermodel.CellStyle;
13 import org.apache.poi.ss.usermodel.CreationHelper;
14 import org.apache.poi.ss.usermodel.Font;
15 import org.apache.poi.ss.usermodel.IndexedColors;
16 import org.apache.poi.ss.usermodel.Row;
17 import org.apache.poi.ss.usermodel.Sheet;
18 import org.apache.poi.ss.usermodel.Workbook;
19
20 public class SummaryHSSF {
21 public static void main(String[] args) throws IOException {
22 //创建Workbook对象（这一个对象代表着对应的一个Excel文件）
23 //HSSFWorkbook表示以xls为后缀名的文件
24 Workbook wb = new HSSFWorkbook();
25 //获得CreationHelper对象,这个应该是一个帮助类
26 CreationHelper helper = wb.getCreationHelper();
27 //创建Sheet并给名字(表示Excel的一个Sheet)
28 Sheet sheet1 = wb.createSheet("HSSF_Sheet_1");
29 Sheet sheet2 = wb.createSheet("HSSF_Sheet_2");
30 //Row表示一行Cell表示一列
31 Row row = null;
32 Cell cell = null;
33 for(int i=0;i<60;i=i+2){
34 //获得这个sheet的第i行
35 row = sheet1.createRow(i);
36 //设置行长度自动
37 //row.setHeight((short)500);
38 row.setHeightInPoints(20);
39 //row.setZeroHeight(true);
40 for(int j=0;j<25;j++){
41 //设置每个sheet每一行的宽度,自动,根据需求自行确定
42 sheet1.autoSizeColumn(j+1, true);
43 //创建一个基本的样式
44 CellStyle cellStyle = SummaryHSSF.createStyleCell(wb);
45 //获得这一行的每j列
46 cell = row.createCell(j);
47 if(j==0){
48 //设置文字在单元格里面的位置
49 cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
50 //先创建字体样式,并把这个样式加到单元格的字体里面
51 cellStyle.setFont(createFonts(wb));
52 //把这个样式加到单元格里面
53 cell.setCellStyle(cellStyle);
54 //给单元格设值
55 cell.setCellValue(true);
56 }else if(j==1){
57 //设置文字在单元格里面的位置
58 cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
59 //设置这个样式的格式(Format)
60 cellStyle = SummaryHSSF.setCellFormat(helper,cellStyle, "#,##0.0000");
61 //先创建字体样式,并把这个样式加到单元格的字体里面
62 cellStyle.setFont(createFonts(wb));
63 //把这个样式加到单元格里面
64 cell.setCellStyle(cellStyle);
65 //给单元格设值
66 cell.setCellValue(new Double(2008.2008));
67 }else if(j==2){
68 cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
69 cellStyle.setFont(createFonts(wb));
70 cell.setCellStyle(cellStyle);
71 cell.setCellValue(helper.createRichTextString("RichString"+i+j));
72 }else if(j==3){
73 cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
74 cellStyle = SummaryHSSF.setCellFormat(helper,cellStyle, "YYYY-MM-DD");
75 cell.setCellStyle(cellStyle);
76 cell.setCellValue(new Date());
77 }else if(j==24){
78 cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
79 cellStyle.setFont(createFonts(wb));
80 //设置公式
81 cell.setCellFormula("SUM(E"+(i+1)+":X"+(i+1)+")");
82 }else{
83 cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
84 cellStyle = SummaryHSSF.setFillBackgroundColors(cellStyle,IndexedColors.ORANGE.getIndex(),IndexedColors.ORANGE.getIndex(),CellStyle.SOLID_FOREGROUND);
85 cell.setCellStyle(cellStyle);
86 cell.setCellValue(1);
87 }
88 }
89 }
90 //输出
91 OutputStream os = new FileOutputStream(new File("c://SummaryHSSF.xls"));
92 wb.write(os);
93 os.close();
94 }
95
96 public static CellStyle createStyleCell(Workbook wb){
97 CellStyle cellStyle = wb.createCellStyle();
98 //设置一个单元格边框颜色
99 cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
100 cellStyle.setBorderTop(CellStyle.BORDER_THIN);
101 cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
102 cellStyle.setBorderRight(CellStyle.BORDER_THIN);
103 //设置一个单元格边框颜色
104 cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
105 cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
106 cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
107 cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
108 return cellStyle;
109 }
110
111 public static CellStyle setCellStyleAlignment(CellStyle cellStyle,short halign,short valign){
112 //设置上下
113 cellStyle.setAlignment(halign);
114 //设置左右
115 cellStyle.setVerticalAlignment(valign);
116 return cellStyle;
117 }
118
119 public static CellStyle setCellFormat(CreationHelper helper,CellStyle cellStyle,String fmt){
120 //还可以用其它方法创建format
121 cellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt));
122 return cellStyle;
123 }
124
125 public static CellStyle setFillBackgroundColors(CellStyle cellStyle,short bg,short fg,short fp){
126 cellStyle.setFillBackgroundColor(bg);
127 cellStyle.setFillForegroundColor(fg);
128 cellStyle.setFillPattern(fp);
129 return cellStyle;
130 }
131
132 public static Font createFonts(Workbook wb){
133 //创建Font对象
134 Font font = wb.createFont();
135 //设置字体
136 font.setFontName("黑体");
137 //着色
138 font.setColor(HSSFColor.BLUE.index);
139 //斜体
140 font.setItalic(true);
141 //字体大小
142 font.setFontHeight((short)300);
143 return font;
144 }
145 }

1 package test.worksheet;
2
3
4 import java.io.File;
5 import java.io.FileInputStream;
6 import java.io.InputStream;
7
8 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
9 import org.apache.poi.ss.usermodel.Cell;
10 import org.apache.poi.ss.usermodel.DateUtil;
11 import org.apache.poi.ss.usermodel.Row;
12 import org.apache.poi.ss.usermodel.Sheet;
13 import org.apache.poi.ss.usermodel.Workbook;
14 import org.apache.poi.ss.usermodel.WorkbookFactory;
15
16 public class ReadExcel {
17 public static void main(String[] args) throws Exception {
18 InputStream is = new FileInputStream(new File("c://SummaryHSSF.xls"));
19 //根据输入流创建Workbook对象
20 Workbook wb = WorkbookFactory.create(is);
21 //get到Sheet对象
22 Sheet sheet = wb.getSheetAt(0);
23 //这个必须用接口
24 for(Row row : sheet){
25 for(Cell cell : row){
26 //cell.getCellType是获得cell里面保存的值的type
27 //如Cell.CELL_TYPE_STRING
28 switch(cell.getCellType()){
29 case Cell.CELL_TYPE_BOOLEAN:
30 //得到Boolean对象的方法
31 System.out.print(cell.getBooleanCellValue()+" ");
32 break;
33 case Cell.CELL_TYPE_NUMERIC:
34 //先看是否是日期格式
35 if(DateUtil.isCellDateFormatted(cell)){
36 //读取日期格式
37 System.out.print(cell.getDateCellValue()+" ");
38 }else{
39 //读取数字
40 System.out.print(cell.getNumericCellValue()+" ");
41 }
42 break;
43 case Cell.CELL_TYPE_FORMULA:
44 //读取公式
45 System.out.print(cell.getCellFormula()+" ");
46 break;
47 case Cell.CELL_TYPE_STRING:
48 //读取String
49 System.out.print(cell.getRichStringCellValue().toString()+" ");
50 break;
51 }
52 }
53 System.out.println("");
54 }
55 }
56 }