POI操作Excel详解，HSSF和XSSF两种方式

时间：2023-03-08 17:28:26
  1. package com.tools.poi.lesson1;
  2. import java.io.FileInputStream;
  3. import java.io.FileNotFoundException;
  4. import java.io.FileOutputStream;
  5. import java.io.IOException;
  6. import java.text.ParseException;
  7. import java.text.SimpleDateFormat;
  8. import java.util.ArrayList;
  9. import java.util.List;
  10. import org.apache.poi.hssf.usermodel.HSSFCell;
  11. import org.apache.poi.hssf.usermodel.HSSFCellStyle;
  12. import org.apache.poi.hssf.usermodel.HSSFRow;
  13. import org.apache.poi.hssf.usermodel.HSSFSheet;
  14. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  15. import org.apache.poi.hssf.util.HSSFColor;
  16. import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  17. import org.apache.poi.ss.usermodel.Cell;
  18. import org.apache.poi.ss.usermodel.CellStyle;
  19. import com.tools.poi.bean.Student;
  20. public class ExcelUtilWithHSSF {
  21. public static void main(String[] args) {
  22. try {
  23. getExcelAsFile("aaa");
  24. } catch (FileNotFoundException e) {
  25. e.printStackTrace();
  26. } catch (IOException e) {
  27. e.printStackTrace();
  28. }
  29. //      try {
  30. //          CreateExcelDemo1();
  31. //      } catch (ParseException e) {
  32. //          e.printStackTrace();
  33. //      }
  34. }
  35. /**
  36. * 得到Excel，并解析内容
  37. * @param file
  38. * @throws FileNotFoundException
  39. * @throws IOException
  40. */
  41. public static void getExcelAsFile(String file) throws FileNotFoundException, IOException{
  42. //1.得到Excel常用对象
  43. //      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/test.xls"));
  44. POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/new1.xls"));
  45. //2.得到Excel工作簿对象
  46. HSSFWorkbook wb = new HSSFWorkbook(fs);
  47. //3.得到Excel工作表对象
  48. HSSFSheet sheet = wb.getSheetAt(0);
  49. //总行数
  50. int trLength = sheet.getLastRowNum();
  51. //4.得到Excel工作表的行
  52. HSSFRow row = sheet.getRow(0);
  53. //总列数
  54. int tdLength = row.getLastCellNum();
  55. //5.得到Excel工作表指定行的单元格
  56. HSSFCell cell = row.getCell((short)1);
  57. //6.得到单元格样式
  58. CellStyle cellStyle = cell.getCellStyle();
  59. for(int i=0;i<trLength;i++){
  60. //得到Excel工作表的行
  61. HSSFRow row1 = sheet.getRow(i);
  62. for(int j=0;j<tdLength;j++){
  63. //得到Excel工作表指定行的单元格
  64. HSSFCell cell1 = row1.getCell(j);
  65. /**
  66. * 为了处理：Excel异常Cannot get a text value from a numeric cell
  67. * 将所有列中的内容都设置成String类型格式
  68. */
  69. if(cell1!=null){
  70. cell1.setCellType(Cell.CELL_TYPE_STRING);
  71. }
  72. //获得每一列中的值
  73. System.out.print(cell1.getStringCellValue()+"\t\t\t");
  74. }
  75. System.out.println();
  76. }
  77. }
  78. /**
  79. * 创建Excel，并写入内容
  80. */
  81. public static void CreateExcel(){
  82. //1.创建Excel工作薄对象
  83. HSSFWorkbook wb = new HSSFWorkbook();
  84. //2.创建Excel工作表对象
  85. HSSFSheet sheet = wb.createSheet("new Sheet");
  86. //3.创建Excel工作表的行
  87. HSSFRow row = sheet.createRow(6);
  88. //4.创建单元格样式
  89. CellStyle cellStyle =wb.createCellStyle();
  90. // 设置这些样式
  91. cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
  92. cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  93. cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  94. cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  95. cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  96. cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  97. cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  98. //5.创建Excel工作表指定行的单元格
  99. row.createCell(0).setCellStyle(cellStyle);
  100. //6.设置Excel工作表的值
  101. row.createCell(0).setCellValue("aaaa");
  102. row.createCell(1).setCellStyle(cellStyle);
  103. row.createCell(1).setCellValue("bbbb");
  104. //设置sheet名称和单元格内容
  105. wb.setSheetName(0,"第一张工作表");
  106. //设置单元格内容   cell.setCellValue("单元格内容");
  107. // 最后一步，将文件存到指定位置
  108. try
  109. {
  110. FileOutputStream fout = new FileOutputStream("E:/students.xls");
  111. wb.write(fout);
  112. fout.close();
  113. }
  114. catch (Exception e)
  115. {
  116. e.printStackTrace();
  117. }
  118. }
  119. /**
  120. * 创建Excel的实例
  121. * @throws ParseException
  122. */
  123. public static void CreateExcelDemo1() throws ParseException{
  124. List list = new ArrayList();
  125. SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
  126. Student user1 = new Student(1, "张三", 16,true, df.parse("1997-03-12"));
  127. Student user2 = new Student(2, "李四", 17,true, df.parse("1996-08-12"));
  128. Student user3 = new Student(3, "王五", 26,false, df.parse("1985-11-12"));
  129. list.add(user1);
  130. list.add(user2);
  131. list.add(user3);
  132. // 第一步，创建一个webbook，对应一个Excel文件
  133. HSSFWorkbook wb = new HSSFWorkbook();
  134. // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
  135. HSSFSheet sheet = wb.createSheet("学生表一");
  136. // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
  137. HSSFRow row = sheet.createRow((int) 0);
  138. // 第四步，创建单元格，并设置值表头 设置表头居中
  139. HSSFCellStyle style = wb.createCellStyle();
  140. style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
  141. HSSFCell cell = row.createCell((short) 0);
  142. cell.setCellValue("学号");
  143. cell.setCellStyle(style);
  144. cell = row.createCell((short) 1);
  145. cell.setCellValue("姓名");
  146. cell.setCellStyle(style);
  147. cell = row.createCell((short) 2);
  148. cell.setCellValue("年龄");
  149. cell.setCellStyle(style);
  150. cell = row.createCell((short) 3);
  151. cell.setCellValue("性别");
  152. cell.setCellStyle(style);
  153. cell = row.createCell((short) 4);
  154. cell.setCellValue("生日");
  155. cell.setCellStyle(style);
  156. // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
  157. for (int i = 0; i < list.size(); i++)
  158. {
  159. row = sheet.createRow((int) i + 1);
  160. Student stu = (Student) list.get(i);
  161. // 第四步，创建单元格，并设置值
  162. row.createCell((short) 0).setCellValue((double) stu.getId());
  163. row.createCell((short) 1).setCellValue(stu.getName());
  164. row.createCell((short) 2).setCellValue((double) stu.getAge());
  165. row.createCell((short)3).setCellValue(stu.getSex()==true?"男":"女");
  166. cell = row.createCell((short) 4);
  167. cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu
  168. .getBirthday()));
  169. }
  170. // 第六步，将文件存到指定位置
  171. try
  172. {
  173. FileOutputStream fout = new FileOutputStream("E:/students.xls");
  174. wb.write(fout);
  175. fout.close();
  176. }
  177. catch (Exception e)
  178. {
  179. e.printStackTrace();
  180. }
  181. }
  182. }

XSSF方式：

[java] view plain copy
  1. package com.tools.poi.lesson1;
  2. import java.io.File;
  3. import java.io.FileInputStream;
  4. import java.io.FileNotFoundException;
  5. import java.io.FileOutputStream;
  6. import java.io.IOException;
  7. import java.io.InputStream;
  8. import java.io.OutputStream;
  9. import java.text.ParseException;
  10. import java.text.SimpleDateFormat;
  11. import java.util.ArrayList;
  12. import java.util.List;
  13. import org.apache.poi.hssf.usermodel.HSSFCell;
  14. import org.apache.poi.hssf.usermodel.HSSFCellStyle;
  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.openxml4j.exceptions.InvalidFormatException;
  20. import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  21. import org.apache.poi.ss.usermodel.Cell;
  22. import org.apache.poi.ss.usermodel.CellStyle;
  23. import org.apache.poi.ss.usermodel.Row;
  24. import org.apache.poi.ss.usermodel.Sheet;
  25. import org.apache.poi.ss.usermodel.Workbook;
  26. import org.apache.poi.ss.usermodel.WorkbookFactory;
  27. import org.apache.poi.ss.util.WorkbookUtil;
  28. import com.tools.poi.bean.Student;
  29. public class ExcelUtilWithXSSF {
  30. public static void main(String[] args) {
  31. try {
  32. getExcelAsFile("d:/FTP/系统报表.xls");
  33. } catch (FileNotFoundException e) {
  34. e.printStackTrace();
  35. } catch (IOException e) {
  36. e.printStackTrace();
  37. } catch (InvalidFormatException e) {
  38. e.printStackTrace();
  39. }
  40. //      try {
  41. //          CreateExcelDemo1();
  42. //      } catch (ParseException e) {
  43. //          e.printStackTrace();
  44. //      }
  45. }
  46. /**
  47. * 得到Excel，并解析内容  对2007及以上版本 使用XSSF解析
  48. * @param file
  49. * @throws FileNotFoundException
  50. * @throws IOException
  51. * @throws InvalidFormatException
  52. */
  53. public static void getExcelAsFile(String file) throws FileNotFoundException, IOException, InvalidFormatException{
  54. //      //1.得到Excel常用对象
  55. //      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/new1.xls"));
  56. //      //2.得到Excel工作簿对象
  57. //      HSSFWorkbook wb = new HSSFWorkbook(fs);
  58. InputStream ins = null;
  59. Workbook wb = null;
  60. ins=new FileInputStream(new File(file));
  61. //ins= ExcelService.class.getClassLoader().getResourceAsStream(filePath);
  62. wb = WorkbookFactory.create(ins);
  63. ins.close();
  64. //3.得到Excel工作表对象
  65. Sheet sheet = wb.getSheetAt(0);
  66. //总行数
  67. int trLength = sheet.getLastRowNum();
  68. //4.得到Excel工作表的行
  69. Row row = sheet.getRow(0);
  70. //总列数
  71. int tdLength = row.getLastCellNum();
  72. //5.得到Excel工作表指定行的单元格
  73. Cell cell = row.getCell((short)1);
  74. //6.得到单元格样式
  75. CellStyle cellStyle = cell.getCellStyle();
  76. for(int i=5;i<trLength;i++){
  77. //得到Excel工作表的行
  78. Row row1 = sheet.getRow(i);
  79. for(int j=0;j<tdLength;j++){
  80. //得到Excel工作表指定行的单元格
  81. Cell cell1 = row1.getCell(j);
  82. /**
  83. * 为了处理：Excel异常Cannot get a text value from a numeric cell
  84. * 将所有列中的内容都设置成String类型格式
  85. */
  86. if(cell1!=null){
  87. cell1.setCellType(Cell.CELL_TYPE_STRING);
  88. }
  89. if(j==5&&i<=10){
  90. cell1.setCellValue("1000");
  91. }
  92. //获得每一列中的值
  93. System.out.print(cell1+"                   ");
  94. }
  95. System.out.println();
  96. }
  97. //将修改后的数据保存
  98. OutputStream out = new FileOutputStream(file);
  99. wb.write(out);
  100. }
  101. /**
  102. * 创建Excel，并写入内容
  103. */
  104. public static void CreateExcel(){
  105. //1.创建Excel工作薄对象
  106. HSSFWorkbook wb = new HSSFWorkbook();
  107. //2.创建Excel工作表对象
  108. HSSFSheet sheet = wb.createSheet("new Sheet");
  109. //3.创建Excel工作表的行
  110. HSSFRow row = sheet.createRow(6);
  111. //4.创建单元格样式
  112. CellStyle cellStyle =wb.createCellStyle();
  113. // 设置这些样式
  114. cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
  115. cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  116. cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  117. cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  118. cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  119. cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  120. cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  121. //5.创建Excel工作表指定行的单元格
  122. row.createCell(0).setCellStyle(cellStyle);
  123. //6.设置Excel工作表的值
  124. row.createCell(0).setCellValue("aaaa");
  125. row.createCell(1).setCellStyle(cellStyle);
  126. row.createCell(1).setCellValue("bbbb");
  127. //设置sheet名称和单元格内容
  128. wb.setSheetName(0,"第一张工作表");
  129. //设置单元格内容   cell.setCellValue("单元格内容");
  130. // 最后一步，将文件存到指定位置
  131. try
  132. {
  133. FileOutputStream fout = new FileOutputStream("E:/students.xls");
  134. wb.write(fout);
  135. fout.close();
  136. }
  137. catch (Exception e)
  138. {
  139. e.printStackTrace();
  140. }
  141. }
  142. /**
  143. * 创建Excel的实例
  144. * @throws ParseException
  145. */
  146. public static void CreateExcelDemo1() throws ParseException{
  147. List list = new ArrayList();
  148. SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
  149. Student user1 = new Student(1, "张三", 16,true, df.parse("1997-03-12"));
  150. Student user2 = new Student(2, "李四", 17,true, df.parse("1996-08-12"));
  151. Student user3 = new Student(3, "王五", 26,false, df.parse("1985-11-12"));
  152. list.add(user1);
  153. list.add(user2);
  154. list.add(user3);
  155. // 第一步，创建一个webbook，对应一个Excel文件
  156. HSSFWorkbook wb = new HSSFWorkbook();
  157. // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
  158. HSSFSheet sheet = wb.createSheet("学生表一");
  159. // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
  160. HSSFRow row = sheet.createRow((int) 0);
  161. // 第四步，创建单元格，并设置值表头 设置表头居中
  162. HSSFCellStyle style = wb.createCellStyle();
  163. style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
  164. HSSFCell cell = row.createCell((short) 0);
  165. cell.setCellValue("学号");
  166. cell.setCellStyle(style);
  167. cell = row.createCell((short) 1);
  168. cell.setCellValue("姓名");
  169. cell.setCellStyle(style);
  170. cell = row.createCell((short) 2);
  171. cell.setCellValue("年龄");
  172. cell.setCellStyle(style);
  173. cell = row.createCell((short) 3);
  174. cell.setCellValue("性别");
  175. cell.setCellStyle(style);
  176. cell = row.createCell((short) 4);
  177. cell.setCellValue("生日");
  178. cell.setCellStyle(style);
  179. // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
  180. for (int i = 0; i < list.size(); i++)
  181. {
  182. row = sheet.createRow((int) i + 1);
  183. Student stu = (Student) list.get(i);
  184. // 第四步，创建单元格，并设置值
  185. row.createCell((short) 0).setCellValue((double) stu.getId());
  186. row.createCell((short) 1).setCellValue(stu.getName());
  187. row.createCell((short) 2).setCellValue((double) stu.getAge());
  188. row.createCell((short)3).setCellValue(stu.getSex()==true?"男":"女");
  189. cell = row.createCell((short) 4);
  190. cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu
  191. .getBirthday()));
  192. }
  193. // 第六步，将文件存到指定位置
  194. try
  195. {
  196. FileOutputStream fout = new FileOutputStream("E:/students.xls");
  197. wb.write(fout);
  198. fout.close();
  199. }
  200. catch (Exception e)
  201. {
  202. e.printStackTrace();
  203. }
  204. }
  205. }

注意：

修改Excel中的某个内容：

[java] view plain copy
  1. cell1.setCellValue("1000");

保存修改后的Excel文件：

[java] view plain copy
    1. OutputStream out = new FileOutputStream(file);
    2. wb.write(out);

相关文章