POI操作excel详解，HSSF和XSSF两种方式的区别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
package com.tools.poi.lesson1;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;

import com.tools.poi.bean.Student;

public class ExcelUtilWithHSSF {
    public static void main(String[] args) {
        try {
            getExcelAsFile("aaa");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }


//      try {
//          CreateExcelDemo1();
//      } catch (ParseException e) {
//          e.printStackTrace();
//      }


    }

    /**
     * 得到Excel，并解析内容
     * @param file
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static void getExcelAsFile(String file) throws FileNotFoundException, IOException{
        //1.得到Excel常用对象
//      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/test.xls"));
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/new1.xls"));
        //2.得到Excel工作簿对象
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        //3.得到Excel工作表对象
        HSSFSheet sheet = wb.getSheetAt(0);
        //总行数
        int trLength = sheet.getLastRowNum();
        //4.得到Excel工作表的行
        HSSFRow row = sheet.getRow(0);
        //总列数
        int tdLength = row.getLastCellNum();
        //5.得到Excel工作表指定行的单元格
        HSSFCell cell = row.getCell((short)1);
        //6.得到单元格样式
        CellStyle cellStyle = cell.getCellStyle();
        for(int i=0;i<trLength;i++){
            //得到Excel工作表的行
            HSSFRow row1 = sheet.getRow(i);
            for(int j=0;j<tdLength;j++){

            //得到Excel工作表指定行的单元格
            HSSFCell cell1 = row1.getCell(j);

            /**
             * 为了处理：Excel异常Cannot get a text value from a numeric cell
             * 将所有列中的内容都设置成String类型格式
             */
            if(cell1!=null){
                  cell1.setCellType(Cell.CELL_TYPE_STRING);
             }

            //获得每一列中的值
            System.out.print(cell1.getStringCellValue()+"ttt");
            }
            System.out.println();
        }
    }


    /**
     * 创建Excel，并写入内容
     */
    public static void CreateExcel(){

        //1.创建Excel工作薄对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //2.创建Excel工作表对象     
        HSSFSheet sheet = wb.createSheet("new Sheet");
        //3.创建Excel工作表的行   
        HSSFRow row = sheet.createRow(6);
        //4.创建单元格样式
        CellStyle cellStyle =wb.createCellStyle();
          // 设置这些样式
        cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);



        //5.创建Excel工作表指定行的单元格
        row.createCell(0).setCellStyle(cellStyle);
        //6.设置Excel工作表的值
        row.createCell(0).setCellValue("aaaa");

        row.createCell(1).setCellStyle(cellStyle);
        row.createCell(1).setCellValue("bbbb");


        //设置sheet名称和单元格内容
        wb.setSheetName(0,"第一张工作表");
        //设置单元格内容   cell.setCellValue("单元格内容");

        // 最后一步，将文件存到指定位置
                try
                {
                    FileOutputStream fout = new FileOutputStream("E:/students.xls");
                    wb.write(fout);
                    fout.close();
                }
                catch (Exception e)
                {
                    e.printStackTrace();
                }
    }

    /**
     * 创建Excel的实例
     * @throws ParseException 
     */
    public static void CreateExcelDemo1() throws ParseException{
        List list = new ArrayList();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
        Student user1 = new Student(1, "张三", 16,true, df.parse("1997-03-12"));
        Student user2 = new Student(2, "李四", 17,true, df.parse("1996-08-12"));
        Student user3 = new Student(3, "王五", 26,false, df.parse("1985-11-12"));
        list.add(user1);
        list.add(user2);
        list.add(user3);


        // 第一步，创建一个webbook，对应一个Excel文件
                HSSFWorkbook wb = new HSSFWorkbook();
                // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
                HSSFSheet sheet = wb.createSheet("学生表一");
                // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                HSSFRow row = sheet.createRow((int) 0);
                // 第四步，创建单元格，并设置值表头 设置表头居中
                HSSFCellStyle style = wb.createCellStyle();
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

                HSSFCell cell = row.createCell((short) 0);
                cell.setCellValue("学号");
                cell.setCellStyle(style);
                cell = row.createCell((short) 1);
                cell.setCellValue("姓名");
                cell.setCellStyle(style);
                cell = row.createCell((short) 2);
                cell.setCellValue("年龄");
                cell.setCellStyle(style);
                cell = row.createCell((short) 3);
                cell.setCellValue("性别");
                cell.setCellStyle(style);
                cell = row.createCell((short) 4);
                cell.setCellValue("生日");
                cell.setCellStyle(style);

                // 第五步，写入实体数据 实际应用中这些数据从数据库得到，

                for (int i = 0; i < list.size(); i++)
                {
                    row = sheet.createRow((int) i + 1);
                    Student stu = (Student) list.get(i);
                    // 第四步，创建单元格，并设置值
                    row.createCell((short) 0).setCellValue((double) stu.getId());
                    row.createCell((short) 1).setCellValue(stu.getName());
                    row.createCell((short) 2).setCellValue((double) stu.getAge());
                    row.createCell((short)3).setCellValue(stu.getSex()==true?"男":"女");
                    cell = row.createCell((short) 4);
                    cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu
                            .getBirthday()));
                }
                // 第六步，将文件存到指定位置
                try
                {
                    FileOutputStream fout = new FileOutputStream("E:/students.xls");
                    wb.write(fout);
                    fout.close();
                }
                catch (Exception e)
                {
                    e.printStackTrace();
                }



    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
package com.tools.poi.lesson1;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.WorkbookUtil;

import com.tools.poi.bean.Student;

public class ExcelUtilWithXSSF {
    public static void main(String[] args) {
        try {
            getExcelAsFile("d:/FTP/系统报表.xls");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }


//      try {
//          CreateExcelDemo1();
//      } catch (ParseException e) {
//          e.printStackTrace();
//      }


    }

    /**
     * 得到Excel，并解析内容  对2007及以上版本 使用XSSF解析
     * @param file
     * @throws FileNotFoundException
     * @throws IOException
     * @throws InvalidFormatException 
     */
    public static void getExcelAsFile(String file) throws FileNotFoundException, IOException, InvalidFormatException{
//      //1.得到Excel常用对象
//      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/new1.xls"));
//      //2.得到Excel工作簿对象
//      HSSFWorkbook wb = new HSSFWorkbook(fs);



        InputStream ins = null;   
        Workbook wb = null;   
            ins=new FileInputStream(new File(file));   
            //ins= ExcelService.class.getClassLoader().getResourceAsStream(filePath);   
            wb = WorkbookFactory.create(ins);   
            ins.close();   


        //3.得到Excel工作表对象
        Sheet sheet = wb.getSheetAt(0);
        //总行数
        int trLength = sheet.getLastRowNum();
        //4.得到Excel工作表的行
        Row row = sheet.getRow(0);
        //总列数
        int tdLength = row.getLastCellNum();
        //5.得到Excel工作表指定行的单元格
        Cell cell = row.getCell((short)1);
        //6.得到单元格样式
        CellStyle cellStyle = cell.getCellStyle();

        for(int i=5;i<trLength;i++){
            //得到Excel工作表的行
            Row row1 = sheet.getRow(i);
            for(int j=0;j<tdLength;j++){
            //得到Excel工作表指定行的单元格
            Cell cell1 = row1.getCell(j);
            /**
             * 为了处理：Excel异常Cannot get a text value from a numeric cell
             * 将所有列中的内容都设置成String类型格式
             */
            if(cell1!=null){
                  cell1.setCellType(Cell.CELL_TYPE_STRING);
             }

            if(j==5&&i<=10){
                cell1.setCellValue("1000");
            }

            //获得每一列中的值
            System.out.print(cell1+"                   ");
            }
            System.out.println();
        }

        //将修改后的数据保存
        OutputStream out = new FileOutputStream(file);
                wb.write(out);
    }


    /**
     * 创建Excel，并写入内容
     */
    public static void CreateExcel(){

        //1.创建Excel工作薄对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //2.创建Excel工作表对象     
        HSSFSheet sheet = wb.createSheet("new Sheet");
        //3.创建Excel工作表的行   
        HSSFRow row = sheet.createRow(6);
        //4.创建单元格样式
        CellStyle cellStyle =wb.createCellStyle();
          // 设置这些样式
        cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);



        //5.创建Excel工作表指定行的单元格
        row.createCell(0).setCellStyle(cellStyle);
        //6.设置Excel工作表的值
        row.createCell(0).setCellValue("aaaa");

        row.createCell(1).setCellStyle(cellStyle);
        row.createCell(1).setCellValue("bbbb");


        //设置sheet名称和单元格内容
        wb.setSheetName(0,"第一张工作表");
        //设置单元格内容   cell.setCellValue("单元格内容");

        // 最后一步，将文件存到指定位置
                try
                {
                    FileOutputStream fout = new FileOutputStream("E:/students.xls");
                    wb.write(fout);
                    fout.close();
                }
                catch (Exception e)
                {
                    e.printStackTrace();
                }
    }

    /**
     * 创建Excel的实例
     * @throws ParseException 
     */
    public static void CreateExcelDemo1() throws ParseException{
        List list = new ArrayList();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
        Student user1 = new Student(1, "张三", 16,true, df.parse("1997-03-12"));
        Student user2 = new Student(2, "李四", 17,true, df.parse("1996-08-12"));
        Student user3 = new Student(3, "王五", 26,false, df.parse("1985-11-12"));
        list.add(user1);
        list.add(user2);
        list.add(user3);


        // 第一步，创建一个webbook，对应一个Excel文件
                HSSFWorkbook wb = new HSSFWorkbook();
                // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
                HSSFSheet sheet = wb.createSheet("学生表一");
                // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                HSSFRow row = sheet.createRow((int) 0);
                // 第四步，创建单元格，并设置值表头 设置表头居中
                HSSFCellStyle style = wb.createCellStyle();
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

                HSSFCell cell = row.createCell((short) 0);
                cell.setCellValue("学号");
                cell.setCellStyle(style);
                cell = row.createCell((short) 1);
                cell.setCellValue("姓名");
                cell.setCellStyle(style);
                cell = row.createCell((short) 2);
                cell.setCellValue("年龄");
                cell.setCellStyle(style);
                cell = row.createCell((short) 3);
                cell.setCellValue("性别");
                cell.setCellStyle(style);
                cell = row.createCell((short) 4);
                cell.setCellValue("生日");
                cell.setCellStyle(style);

                // 第五步，写入实体数据 实际应用中这些数据从数据库得到，

                for (int i = 0; i < list.size(); i++)
                {
                    row = sheet.createRow((int) i + 1);
                    Student stu = (Student) list.get(i);
                    // 第四步，创建单元格，并设置值
                    row.createCell((short) 0).setCellValue((double) stu.getId());
                    row.createCell((short) 1).setCellValue(stu.getName());
                    row.createCell((short) 2).setCellValue((double) stu.getAge());
                    row.createCell((short)3).setCellValue(stu.getSex()==true?"男":"女");
                    cell = row.createCell((short) 4);
                    cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu
                            .getBirthday()));
                }
                // 第六步，将文件存到指定位置
                try
                {
                    FileOutputStream fout = new FileOutputStream("E:/students.xls");
                    wb.write(fout);
                    fout.close();
                }
                catch (Exception e)
                {
                    e.printStackTrace();
                }



    }
}

1
cell1.setCellValue("1000");

1
2
OutputStream out = new FileOutputStream(file);
wb.write(out);