java之导入excel
程序员文章站
2022-03-12 18:14:38
接口: 1 /** 2 * 3 * Description: 导入excel表 4 * @param map 5 * @param request 6 * @param session 7 * @return 8 * @author shiqianyu 9 * @throws Exception 1 ......
接口:
1 /**
2 *
3 * Description: 导入excel表
4 * @param map
5 * @param request
6 * @param session
7 * @return
8 * @author shiqianyu
9 * @throws Exception
10 * @Data 2018年3月12日 下午2:40:52
11 */
12 @RequestMapping("/importExcelData")
13 public ModelAndView importExcelData(ModelMap map,HttpServletRequest request,HttpSession session,@RequestParam(value = "file", required = true)MultipartFile multfile) throws Exception{
14 //获取页面信息
15 Map<String, String> pageCrawlValue = StringUtil.getArrayValue(request.getParameterMap());
16 pageCrawlValue.put("CRAWLRESULTPATH", pageCrawlValue.get("CRAWLRESULTPATH")+File.separator+pageCrawlValue.get("PAGECRAWLNAME"));
17 PageCrawlTaskManage pageCrawlTaskManage = new PageCrawlTaskManage();
18 pageCrawlTaskManage.setRegx(pageCrawlValue.get("PageRegx"));
19 pageCrawlTaskManage.setPage_prdfix(pageCrawlValue.get("PAGE_PRDFIX"));
20 pageCrawlTaskManage.setCtCycle(pageCrawlValue.get("CTCYCLE"));
21 pageCrawlTaskManage.setCrawlResultPath(pageCrawlValue.get("CRAWLRESULTPATH"));
22 pageCrawlTaskManage.setProxyDeployId(pageCrawlValue.get("PROXYID"));
23 pageCrawlTaskManage.setCrawlIntervalTime(Integer.valueOf(pageCrawlValue.get("CRAWL_INTERVAL_TIME")));
24 pageCrawlTaskManage.setPageCrawlName(pageCrawlValue.get("PAGECRAWLNAME"));
25 pageCrawlTaskManage.setIsDrill(pageCrawlValue.get("isDrill"));
26 pageCrawlTaskManage.setCtCycleUnit(pageCrawlValue.get("CTCYCLEUNIT"));
27 pageCrawlTaskManage.setDataSourceId(pageCrawlValue.get("DATASOURCEID"));
28 pageCrawlTaskManage.setPage_start(pageCrawlValue.get("PAGE_START"));
29 pageCrawlTaskManage.setPage_end(pageCrawlValue.get("PAGE_END"));
30 pageCrawlTaskManage.setDescribe(pageCrawlValue.get("describe"));
31 pageCrawlTaskManage.setThreadCount(pageCrawlValue.get("CTTHREADCOUNT"));
32 pageCrawlTaskManage.setPage_suffix(pageCrawlValue.get("PAGE_SUFFIX"));
33
34 int startRow = Integer.parseInt(pageCrawlValue.get("startRow"))-1;//导入excel 起始行号 -3
35 int endRow = Integer.parseInt(pageCrawlValue.get("endRow"))-1; //导入excel 结束行号 -3
36 int column = Integer.parseInt(pageCrawlValue.get("column"))-1; //导入 excel指定的列号 -1
37
38 //获取页码后缀
39 List<String> suffixs = new ArrayList<String>();
40
41
42 //检查文件
43 ExcelData.checkFile(multfile);
44 //获得Workbook工作薄对象
45 Workbook workbook = ExcelData.getWorkBook(multfile);
46 //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
47 List<String[]> list = new ArrayList<String[]>();
48 if(workbook != null){
49 for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
50 //获得当前sheet工作表
51 Sheet sheet = workbook.getSheetAt(sheetNum);
52 if(sheet == null){
53 continue;
54 }
55 //获得当前sheet的开始行
56 int firstRowNum = startRow;
57 //获得当前sheet的结束行
58 int lastRowNum = endRow;
59 //循环行
60 for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){ //firstRowNum+1;
61 //获得当前行
62 Row row = sheet.getRow(rowNum);
63 if(row == null){
64 continue;
65 }
66 //获得当前行的开始列
67 int firstCellNum = row.getFirstCellNum();
68 //获得当前行的列数
69 int lastCellNum = row.getLastCellNum();
70 String[] cells = new String[row.getLastCellNum()];
71 //循环当前行
72 for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
73 if(cellNum==column){
74 Cell cell = row.getCell(cellNum);
75 cells[cellNum] = ExcelData.getCellValue(cell);
76 }
77
78 }
79 list.add(cells);
80 }
81 }
82 }
83
84
85 for(int i= 0;i<list.size();i++){//startRow
86 String[] arr=null;
87 arr = list.get(i);//[张飞, As255, c2, d2, f2]
88 if(arr!=null){
89 //遍历列
90 for(int j=0;j<arr.length;j++){
91 if(j==column){
92 suffixs.add(arr[j]);//As255
93 }
94 }
95 }
96 }
97
98 map.put("pageCrawlTaskManage", pageCrawlTaskManage);
99 map.put("times",suffixs);
100 return new ModelAndView("crawl/CrawlTaskToExcel");
101 }
工具类:
1 package com.dimensoft.splider.util;
2
3 import java.io.IOException;
4 import java.io.InputStream;
5 import java.text.DecimalFormat;
6 import java.text.SimpleDateFormat;
7 import java.util.ArrayList;
8 import java.util.Date;
9 import java.util.List;
10
11 import org.apache.log4j.Logger;
12 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
13 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
14 import org.apache.poi.ss.usermodel.Cell;
15 import org.apache.poi.ss.usermodel.CellStyle;
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 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
20 import org.springframework.web.multipart.MultipartFile;
21
22
23 /**
24 * 解析excel 上传数据
25 * @author shiqianyu
26 *
27 */
28 public class ExcelData {
29
30 private static final Logger log = Logger.getLogger(ExcelData.class);
31
32 public static List<String[]> getExcelData(MultipartFile file) throws IOException{
33 checkFile(file);
34 //获得Workbook工作薄对象
35 Workbook workbook = getWorkBook(file);
36 //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
37 List<String[]> list = new ArrayList<String[]>();
38 if(workbook != null){
39 for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
40 //获得当前sheet工作表
41 Sheet sheet = workbook.getSheetAt(sheetNum);
42 if(sheet == null){
43 continue;
44 }
45 //获得当前sheet的开始行
46 int firstRowNum = sheet.getFirstRowNum();
47 //获得当前sheet的结束行
48 int lastRowNum = sheet.getLastRowNum();
49 //循环除了第一行的所有行
50 for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
51 //获得当前行
52 Row row = sheet.getRow(rowNum);
53 if(row == null){
54 continue;
55 }
56 //获得当前行的开始列
57 int firstCellNum = row.getFirstCellNum();
58 //获得当前行的列数
59 int lastCellNum = row.getLastCellNum();
60 String[] cells = new String[row.getLastCellNum()];
61 //循环当前行
62 for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
63 Cell cell = row.getCell(cellNum);
64 cells[cellNum] = getCellValue(cell);
65 }
66 list.add(cells);
67 }
68 }
69 }
70 return list;
71 }
72
73
74 /**
75 * 检查文件
76 * @param file
77 * @throws IOException
78 */
79 public static void checkFile(MultipartFile file) throws IOException{
80 //判断文件是否存在
81 if(null == file){
82 log.error("文件不存在！");
83 }
84 //获得文件名
85 String fileName = file.getOriginalFilename();
86 //判断文件是否是excel文件
87 if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
88 log.error(fileName + "不是excel文件");
89 }
90 }
91
92
93 public static Workbook getWorkBook(MultipartFile file) {
94 //获得文件名
95 String fileName = file.getOriginalFilename();
96 //创建Workbook工作薄对象，表示整个excel
97 Workbook workbook = null;
98 try {
99 //获取excel文件的io流
100 InputStream is = file.getInputStream();
101 //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
102 if(fileName.endsWith("xls")){
103 //2003
104 workbook = new HSSFWorkbook(is);
105 }else if(fileName.endsWith("xlsx")){
106 //2007 及2007以上
107 workbook = new XSSFWorkbook(is);
108 }
109 } catch (IOException e) {
110 log.error(e.getMessage());
111 }
112 return workbook;
113 }
114
115 public static String getCellValue(Cell cell){
116 String cellValue = "";
117 if(cell == null){
118 return cellValue;
119 }
120 //判断数据的类型
121 switch (cell.getCellType()){
122 case Cell.CELL_TYPE_NUMERIC: //数字
123 cellValue = stringDateProcess(cell);
124 break;
125 case Cell.CELL_TYPE_STRING: //字符串
126 cellValue = String.valueOf(cell.getStringCellValue());
127 break;
128 case Cell.CELL_TYPE_BOOLEAN: //Boolean
129 cellValue = String.valueOf(cell.getBooleanCellValue());
130 break;
131 case Cell.CELL_TYPE_FORMULA: //公式
132 cellValue = String.valueOf(cell.getCellFormula());
133 break;
134 case Cell.CELL_TYPE_BLANK: //空值
135 cellValue = "";
136 break;
137 case Cell.CELL_TYPE_ERROR: //故障
138 cellValue = "非法字符";
139 break;
140 default:
141 cellValue = "未知类型";
142 break;
143 }
144 return cellValue;
145 }
146
147 /**
148 * 时间格式处理
149 * @return
150 * @author Liu Xin Nan
151 * @data 2017年11月27日
152 */
153 public static String stringDateProcess(Cell cell){
154 String result = new String();
155 if (true) {// 处理日期格式、时间格式
156 SimpleDateFormat sdf = null;
157 if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
158 sdf = new SimpleDateFormat("HH:mm");
159 } else {// 日期
160 sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
161 }
162 Date date = cell.getDateCellValue();
163 result = sdf.format(date);
164 } else if (cell.getCellStyle().getDataFormat() == 58) {
165 // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
166 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
167 double value = cell.getNumericCellValue();
168 Date date = org.apache.poi.ss.usermodel.DateUtil
169 .getJavaDate(value);
170 result = sdf.format(date);
171 } else {
172 double value = cell.getNumericCellValue();
173 CellStyle style = cell.getCellStyle();
174 DecimalFormat format = new DecimalFormat();
175 String temp = style.getDataFormatString();
176 // 单元格设置成常规
177 if (temp.equals("General")) {
178 format.applyPattern("#");
179 }
180 result = format.format(value);
181 }
182
183 return result;
184 }
185 }