POI实现Excel导入导出

利用idea创建java web的maven项目，在pom中添加对poi的jar的依赖。

 1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 2   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
 3   <modelVersion>4.0.0</modelVersion>
 4   <groupId>com.hand</groupId>
 5   <artifactId>excel-data</artifactId>
 6   <packaging>war</packaging>
 7   <version>1.0-SNAPSHOT</version>
 8   <name>excel-data Maven Webapp</name>
 9   <url>http://maven.apache.org</url>
10   <dependencies>
11     <dependency>
12       <groupId>junit</groupId>
13       <artifactId>junit</artifactId>
14       <version>3.8.1</version>
15       <scope>test</scope>
16     </dependency>
17     <dependency>
18       <groupId>org.apache.poi</groupId>
19       <artifactId>poi</artifactId>
20       <version>3.14</version>
21     </dependency>
22   </dependencies>
23   <build>
24     <finalName>excel-data</finalName>
25   </build>
26 </project>

web.xml中配置下后续导出时，所需要的servlet映射信息：

 1 <!DOCTYPE web-app PUBLIC
 2  "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 3  "http://java.sun.com/dtd/web-app_2_3.dtd" >
 4 
 5 <web-app>
 6   <display-name>Archetype Created Web Application</display-name>
 7   <servlet>
 8     <servlet-name>TestServlet</servlet-name>
 9     <servlet-class>TestServlet</servlet-class>
10   </servlet>
11 
12   <servlet-mapping>
13     <servlet-name>TestServlet</servlet-name>
14     <url-pattern>/TestServlet</url-pattern>
15   </servlet-mapping>
16 </web-app>

Excel导入工具类实现：

  1 import org.apache.poi.hssf.usermodel.*;
  2 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  3 import java.io.FileInputStream;
  4 import java.io.FileNotFoundException;
  5 import java.io.IOException;
  6 import java.io.InputStream;
  7 import java.text.SimpleDateFormat;
  8 import java.util.Date;
  9 import java.util.HashMap;
 10 import java.util.Map;
 11 
 12 /**
 13  * @author jiaqing.xu@hand-china.com
 14  * @version 1.0
 15  * @name
 16  * @description 读取并解析excel
 17  * @date 2017/10/19
 18  */
 19 public class ImportExcel {
 20 
 21     private POIFSFileSystem fs;
 22     private HSSFWorkbook wb;
 23     private HSSFSheet sheet;
 24     private HSSFRow row;
 25 
 26     /**
 27      * 读取Excel表格表头的内容
 28      * @param is
 29      * @return String 表头内容的数组
 30      */
 31     public String[] readExcelTitle(InputStream is) {
 32         try {
 33             fs = new POIFSFileSystem(is);
 34             wb = new HSSFWorkbook(fs);
 35         } catch (IOException e) {
 36             e.printStackTrace();
 37         }
 38         sheet = wb.getSheetAt(0);
 39         //得到首行的row
 40         row = sheet.getRow(0);
 41         // 标题总列数
 42         int colNum = row.getPhysicalNumberOfCells();
 43         String[] title = new String[colNum];
 44         for (int i = 0; i < colNum; i++) {
 45             title[i] = getCellFormatValue(row.getCell((short) i));
 46         }
 47         return title;
 48     }
 49 
 50     /**
 51      * 读取Excel数据内容
 52      * @param is
 53      * @return Map 包含单元格数据内容的Map对象
 54      */
 55     public Map<Integer, String> readExcelContent(InputStream is) {
 56         Map<Integer, String> content = new HashMap<Integer, String>();
 57         String str = "";
 58         try {
 59             fs = new POIFSFileSystem(is);
 60             wb = new HSSFWorkbook(fs);
 61         } catch (IOException e) {
 62             e.printStackTrace();
 63         }
 64         sheet = wb.getSheetAt(0);
 65         // 得到总行数
 66         int rowNum = sheet.getLastRowNum();
 67         //由于第0行和第一行已经合并了  在这里索引从2开始
 68         row = sheet.getRow(2);
 69         int colNum = row.getPhysicalNumberOfCells();
 70         // 正文内容应该从第二行开始,第一行为表头的标题
 71         for (int i = 2; i <= rowNum; i++) {
 72             row = sheet.getRow(i);
 73             int j = 0;
 74             while (j < colNum) {
 75                 str += getCellFormatValue(row.getCell((short) j)).trim() + "-";
 76                 j++;
 77             }
 78             content.put(i, str);
 79             str = "";
 80         }
 81         return content;
 82     }
 83 
 84     /**
 85      * 获取单元格数据内容为字符串类型的数据
 86      *
 87      * @param cell Excel单元格
 88      * @return String 单元格数据内容
 89      */
 90     private String getStringCellValue(HSSFCell cell) {
 91         String strCell = "";
 92         switch (cell.getCellType()) {
 93             case HSSFCell.CELL_TYPE_STRING:
 94                 strCell = cell.getStringCellValue();
 95                 break;
 96             case HSSFCell.CELL_TYPE_NUMERIC:
 97                 strCell = String.valueOf(cell.getNumericCellValue());
 98                 break;
 99             case HSSFCell.CELL_TYPE_BOOLEAN:
100                 strCell = String.valueOf(cell.getBooleanCellValue());
101                 break;
102             case HSSFCell.CELL_TYPE_BLANK:
103                 strCell = "";
104                 break;
105             default:
106                 strCell = "";
107                 break;
108         }
109         if (strCell.equals("") || strCell == null) {
110             return "";
111         }
112         if (cell == null) {
113             return "";
114         }
115         return strCell;
116     }
117 
118     /**
119      * 获取单元格数据内容为日期类型的数据
120      *
121      * @param cell
122      *            Excel单元格
123      * @return String 单元格数据内容
124      */
125     private String getDateCellValue(HSSFCell cell) {
126         String result = "";
127         try {
128             int cellType = cell.getCellType();
129             if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
130                 Date date = cell.getDateCellValue();
131                 result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
132                         + "-" + date.getDate();
133             } else if (cellType == HSSFCell.CELL_TYPE_STRING) {
134                 String date = getStringCellValue(cell);
135                 result = date.replaceAll("[年月]", "-").replace("日", "").trim();
136             } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
137                 result = "";
138             }
139         } catch (Exception e) {
140             System.out.println("日期格式不正确!");
141             e.printStackTrace();
142         }
143         return result;
144     }
145 
146     /**
147      * 根据HSSFCell类型设置数据
148      * @param cell
149      * @return
150      */
151     private String getCellFormatValue(HSSFCell cell) {
152         String cellvalue = "";
153         if (cell != null) {
154             // 判断当前Cell的Type
155             switch (cell.getCellType()) {
156                 // 如果当前Cell的Type为NUMERIC
157                 case HSSFCell.CELL_TYPE_NUMERIC:
158                 case HSSFCell.CELL_TYPE_FORMULA: {
159                     // 判断当前的cell是否为Date
160                     if (HSSFDateUtil.isCellDateFormatted(cell)) {
161                         Date date = cell.getDateCellValue();
162                         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
163                         cellvalue = sdf.format(date);
164                     }
165                     // 如果是纯数字
166                     else {
167                         // 取得当前Cell的数值
168                         cellvalue = String.valueOf(cell.getNumericCellValue());
169                     }
170                     break;
171                 }
172                 // 如果当前Cell的Type为STRIN
173                 case HSSFCell.CELL_TYPE_STRING:
174                     // 取得当前的Cell字符串
175                     cellvalue = cell.getRichStringCellValue().getString();
176                     break;
177                 // 默认的Cell值
178                 default:
179                     cellvalue = " ";
180             }
181         } else {
182             cellvalue = "";
183         }
184         return cellvalue;
185 
186     }
187 
188     public static void main(String[] args) {
189         try {
190             // 对读取Excel表格标题测试
191             InputStream is = new FileInputStream("d:\test2.xls");
192             ImportExcel excelReader = new ImportExcel();
193             String[] title = excelReader.readExcelTitle(is);
194             System.out.println("获得Excel表格的标题:");
195             for (String s : title) {
196                 System.out.print(s + " ");
197             }
198             System.out.println();
199 
200             // 对读取Excel表格内容测试
201             InputStream is2 = new FileInputStream("d:\test2.xls");
202             Map<Integer, String> map = excelReader.readExcelContent(is2);
203             System.out.println("获得Excel表格的内容:");
204             //这里由于xls合并了单元格需要对索引特殊处理
205             for (int i = 2; i <= map.size()+1; i++) {
206                 System.out.println(map.get(i));
207             }
208 
209         } catch (FileNotFoundException e) {
210             System.out.println("未找到指定路径的文件!");
211             e.printStackTrace();
212         }
213     }
214 
215 }

将Excel中的数据通过后台程序维护到一个Map集合中，再利用String的split方法以“-”进行分割，得到单个的值，如果想要将这批数据插入到数据库，则去实例化对象的dto，再给dto对应属性赋值，最终写sql再insert到表中。

Excel导出工具类的实现：

  1 import org.apache.poi.hssf.usermodel.*;
  2 import org.apache.poi.hssf.util.HSSFColor;
  3 import org.apache.poi.ss.usermodel.IndexedColors;
  4 import org.apache.poi.ss.util.CellRangeAddress;
  5
  6 import javax.servlet.http.HttpServletRequest;
  7 import javax.servlet.http.HttpServletResponse;
  8 import java.io.IOException;
  9 import java.io.OutputStream;
 10 import java.util.ArrayList;
 11 import java.util.List;
 12 
 13 /**
 14  * @author jiaqing.xu@hand-china.com
 15  * @version 1.0
 16  * @name
 17  * @description
 18  * @date 2017/10/19
 19  */
 20 public class ExportExcel {
 21 
 22     /**
 23      * 显示的导出表的标题
 24      */
 25     private String title;
 26 
 27     /**
 28      * 导出表的列名
 29      */
 30     private String[] columnName;
 31 
 32     /**
 33      * 需要导出的数据集合
 34      */
 35     private List<Object[]> dataList = new ArrayList<Object[]>();
 36 
 37     /**
 38      * 输入流对象
 39      */
 40     private HttpServletRequest request;
 41 
 42     /**
 43      * 输出流对象
 44      */
 45     private HttpServletResponse response;
 46 
 47 
 48     /**
 49      *
 50      * @param title
 51      * @param columnName
 52      * @param dataList
 53      * @param request
 54      * @param response
 55      * @description 构造方法，传入要导出的数据
 56      */
 57     public ExportExcel(String title, String[] columnName, List<Object[]> dataList,HttpServletRequest request,HttpServletResponse response) {
 58         this.dataList = dataList;
 59         this.columnName = columnName;
 60         this.title = title;
 61         this.request = request;
 62         this.response= response;
 63     }
 64 
 65 
 66     /**
 67      * @param
 68      * @return
 69      * @author jiaqing.xu@hand-china.com
 70      * @date 2017/10/19 13:21
 71      * @description 导出数据到excel
 72      */
 73     public void export() throws Exception {
 74 
 75         try {
 76             HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
 77             HSSFSheet sheet = workbook.createSheet(title);                     // 创建工作表
 78 
 79             // 产生表格标题行
 80             HSSFRow rowm = sheet.createRow(0);
 81             HSSFCell cellTiltle = rowm.createCell(0);
 82 
 83             //设置标题和单元格样式
 84             HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);  //获取列头样式对象
 85             HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象
 86 
 87             //合并单元格
 88             sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (columnName.length - 1)));
 89             cellTiltle.setCellStyle(columnTopStyle);
 90             cellTiltle.setCellValue(title);
 91 
 92             // 定义所需列数
 93             int columnNum = columnName.length;
 94             HSSFRow rowRowName = sheet.createRow(2);                 // 在索引2的位置创建行(最顶端的行开始的第二行)
 95 
 96 
 97             // 将列头设置到sheet的单元格中
 98             for (int n = 0; n < columnNum; n++) {
 99                 HSSFCell cellRowName = rowRowName.createCell(n);                  //创建列头对应个数的单元格
100                 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
101                 HSSFRichTextString text = new HSSFRichTextString(columnName[n]);
102                 cellRowName.setCellValue(text);                                    //设置列头单元格的值
103                 cellRowName.setCellStyle(columnTopStyle);                          //设置列头单元格样式
104             }
105 
106             //将查询出的数据设置到sheet对应的单元格中
107             for (int i = 0; i < dataList.size(); i++) {
108                 Object[] obj = dataList.get(i);//遍历每个对象
109                 HSSFRow row = sheet.createRow(i + 3);//创建所需的行数
110                 for (int j = 0; j < obj.length; j++) {
111                     HSSFCell cell = null;   //设置单元格的数据类型
112                     //第一列为数字类型并设置单元格的值
113                     if (j == 0) {
114                         cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
115                         cell.setCellValue(i + 1);
116                     } else {
117                         //其他列为字符串类型并设置单元格的值
118                         cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
119                         if (!"".equals(obj[j]) && obj[j] != null) {
120                             cell.setCellValue(obj[j].toString());
121                         }
122                     }
123                     cell.setCellStyle(style);                                    //设置单元格样式
124                 }
125             }
126 
127 
128             //让列宽随着导出的列长自动适应
129             for (int colNum = 0; colNum < columnNum; colNum++) {
130                 int columnWidth = sheet.getColumnWidth(colNum) / 256;
131                 for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
132                     HSSFRow currentRow;
133                     //当前行未被使用过
134                     if (sheet.getRow(rowNum) == null) {
135                         currentRow = sheet.createRow(rowNum);
136                     } else {
137                         currentRow = sheet.getRow(rowNum);
138                     }
139                     if (currentRow.getCell(colNum) != null) {
140                         //取得当前的单元格
141                         HSSFCell currentCell = currentRow.getCell(colNum);
142                         //如果当前单元格类型为字符串
143                         if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
144                             int length = currentCell.getStringCellValue().getBytes().length;
145                             if (columnWidth < length) {
146                                 //将单元格里面值大小作为列宽度
147                                 columnWidth = length;
148                             }
149                         }
150                     }
151                 }
152                 //再根据不同列单独做下处理
153                 if (colNum == 0) {
154                     sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
155                 } else {
156                     sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
157                 }
158             }
159 
160             if (workbook != null) {
161                 try {
162                     String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
163                     String headStr = "attachment; filename="" + fileName + """;
164                     response.setContentType("APPLICATION/OCTET-STREAM");
165                     response.setHeader("Content-Disposition", headStr);
166                     OutputStream out1 = response.getOutputStream();
167                     workbook.write(out1);
168                 } catch (IOException e) {
169                     e.printStackTrace();
170                 }
171             }
172 
173         } catch (Exception e) {
174             e.printStackTrace();
175         }
176     }
177 
178 
179     /**
180      * @param
181      * @return
182      * @author jiaqing.xu@hand-china.com
183      * @date 2017/10/19 13:31
184      * @description 标题行的单元格样式
185      */
186     public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
187 
188         // 设置字体
189         HSSFFont font = workbook.createFont();
190         //设置字体大小
191         font.setFontHeightInPoints((short) 11);
192         //字体加粗
193         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
194         //设置字体名字
195         font.setFontName("Courier New");
196         //设置样式;
197         HSSFCellStyle style = workbook.createCellStyle();
198         style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
199         //设置底边框;
200         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
201         //设置底边框颜色;
202         style.setBottomBorderColor(HSSFColor.BLACK.index);
203         //设置左边框;
204         style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
205         //设置左边框颜色;
206         style.setLeftBorderColor(HSSFColor.BLACK.index);
207         //设置右边框;
208         style.setBorderRight(HSSFCellStyle.BORDER_THIN);
209         //设置右边框颜色;
210         style.setRightBorderColor(HSSFColor.BLACK.index);
211         //设置顶边框;
212         style.setBorderTop(HSSFCellStyle.BORDER_THIN);
213         //设置顶边框颜色;
214         style.setTopBorderColor(HSSFColor.BLACK.index);
215         //在样式用应用设置的字体;
216         style.setFont(font);
217         //设置自动换行;
218         style.setWrapText(false);
219         //设置水平对齐的样式为居中对齐;
220         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
221         //设置垂直对齐的样式为居中对齐;
222         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
223         return style;
224     }
225 
226     /**
227      * @param
228      * @return
229      * @author jiaqing.xu@hand-china.com
230      * @date 2017/10/19 13:31
231      * @description 列数据信息单元格样式
232      */
233     public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
234         // 设置字体
235         HSSFFont font = workbook.createFont();
236         //设置字体大小
237         //font.setFontHeightInPoints((short)10);
238         //字体加粗
239         //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
240         //设置字体名字
241         font.setFontName("Courier New");
242         //设置样式;
243         HSSFCellStyle style = workbook.createCellStyle();
244         //设置底边框;
245         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
246         //设置底边框颜色;
247         style.setBottomBorderColor(HSSFColor.BLACK.index);
248         //设置左边框;
249         style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
250         //设置左边框颜色;
251         style.setLeftBorderColor(HSSFColor.BLACK.index);
252         //设置右边框;
253         style.setBorderRight(HSSFCellStyle.BORDER_THIN);
254         //设置右边框颜色;
255         style.setRightBorderColor(HSSFColor.BLACK.index);
256         //设置顶边框;
257         style.setBorderTop(HSSFCellStyle.BORDER_THIN);
258         //设置顶边框颜色;
259         style.setTopBorderColor(HSSFColor.BLACK.index);
260         //在样式用应用设置的字体;
261         style.setFont(font);
262         //设置自动换行;
263         style.setWrapText(false);
264         //设置水平对齐的样式为居中对齐;
265         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
266         //设置垂直对齐的样式为居中对齐;
267         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
268         return style;
269     }
270 }

用于测试导出的Servlet程序：

 1 import javax.servlet.ServletException;
 2 import javax.servlet.annotation.WebServlet;
 3 import javax.servlet.http.HttpServlet;
 4 import javax.servlet.http.HttpServletRequest;
 5 import javax.servlet.http.HttpServletResponse;
 6 import java.io.IOException;
 7 import java.util.ArrayList;
 8 import java.util.List;
 9 
10 /**
11  * @author jiaqing.xu@hand-china.com
12  * @version 1.0
13  * @name
14  * @description
15  * @date 2017/10/19
16  */
17 @WebServlet(name = "TestServlet")
18 public class TestServlet extends HttpServlet {
19     @Override
20     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
21         String title = "货运单据导出";
22         String[] columnName = new String[]{"序号","num1","num2"};
23         List<Object[]> dataList = new ArrayList<Object[]>();
24         Object[] objs;
25         for (int i = 0; i <2; i++) {
26             objs = new Object[columnName.length];
27             objs[0] = i;
28             objs[1] = "1";
29             objs[2] = "2";
30             dataList.add(objs);
31         }
32         //实例化工具类
33         ExportExcel ex = new ExportExcel(title, columnName, dataList,request,response);
34         try {
35             //导出excel
36             ex.export();
37         } catch (Exception e) {
38             e.printStackTrace();
39         }
40     }
41 
42     @Override
43     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
44         doPost(request,response);
45     }
46 }