ExcelUtil工具类
package com.pflm.shakeExternal.util; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.crypt.Decryptor; import org.apache.poi.poifs.crypt.EncryptionInfo; import org.apache.poi.poifs.filesystem.NPOIFSFileSystem; import org.apache.poi.ss.format.CellFormatType; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; 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.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelUtil { private static final String XLS = "xls"; private static final String XLSX = "xlsx"; /** * 创建excel文档, * @param list 数据 * @param keys list中map的key数组集合 * @param columnNames excel的列名 * */ public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) { // 创建excel工作簿 Workbook wb = new HSSFWorkbook(); // 创建第一个sheet(页),并命名 Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。 for(int i=0;i<keys.length;i++){ sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // 创建第一行 Row row = sheet.createRow((short) 0); // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // 创建两种字体 Font f = wb.createFont(); Font f2 = wb.createFont(); // 创建第一种字体样式(用于列名) f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // 创建第二种字体样式(用于值) f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // Font f3=wb.createFont(); // f3.setFontHeightInPoints((short) 10); // f3.setColor(IndexedColors.RED.getIndex()); // 设置第一种单元格的样式(用于列名) cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // 设置第二种单元格的样式(用于值) cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //设置列名 for(int i=0;i<columnNames.length;i++){ Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //设置每行每列的值 for (short i = 1; i < list.size(); i++) { // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的 // 创建一行,在页sheet上 Row row1 = sheet.createRow((short) i); // 在row行上创建一个方格 for(short j=0;j<keys.length;j++){ Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; } /** * 得去excel (支持加密文件,但是对xls文件,xlsx文件不支持) * @param f * @param password * @return */ @SuppressWarnings("resource") public static List<String> readExcel(File f,String password){ try{ String fileName = f.getName(); String type = fileName.substring(fileName.indexOf(".")+1); if(XLS.equals(type)){ HSSFWorkbook hwb = null; Sheet sheet = null; if(password!=null && !"".equals(password)){ Biff8EncryptionKey.setCurrentUserPassword(password); NPOIFSFileSystem fs = new NPOIFSFileSystem(f, true); hwb = new HSSFWorkbook(fs); Biff8EncryptionKey.setCurrentUserPassword(null); }else{ hwb = new HSSFWorkbook(new FileInputStream(f)); } hwb = new HSSFWorkbook(new FileInputStream(f)); sheet = hwb.getSheetAt(0); return readCell(sheet); }else{ NPOIFSFileSystem fs = new NPOIFSFileSystem(f, true); EncryptionInfo info = new EncryptionInfo(fs); Decryptor decryptor = Decryptor.getInstance(info); decryptor.verifyPassword(password); InputStream is = decryptor.getDataStream(fs); XSSFWorkbook xwb = new XSSFWorkbook(is); is.close(); XSSFSheet sheet = xwb.getSheetAt(0); return readCell(sheet); } }catch(Exception e){ e.printStackTrace(); } return null; } private static List<String> readCell(Sheet sheet){ List<String> res = new ArrayList<>(); int start_row = sheet.getFirstRowNum(); int end_row = sheet.getLastRowNum(); for(int i=start_row; i<=end_row;i++){ Row row = sheet.getRow(i); int start_cell = row.getFirstCellNum(); int end_cell = row.getLastCellNum(); StringBuilder sb = new StringBuilder(); for(int j = start_cell; j<end_cell; j++){ Cell cell = row.getCell(j); if(cell == null){ continue; } sb.append(","); // CellType type1 = cell.getCellTypeEnum(); // switch(type1){ //获取数据 // case _NONE: // break; // case BLANK: //白单元格 // sb.append(""); // break; // case BOOLEAN: //布尔类型 // break; // case ERROR: //故障 // break; // case FORMULA: //公式 // break; // case NUMERIC: //数值、日期类型 // break; // case STRING: //字符串 // break; // default:// 未知类型 // break; // } sb.append(cell.toString()); } String line = sb.toString(); line = line.substring(1); res.add(line); } return res; } private static Sheet getSheet(InputStream in,String fileName){ Sheet sheet = null; try{ String type = fileName.substring(fileName.indexOf(".")+1); if(XLS.equals(type)){ HSSFWorkbook hwb = hwb = new HSSFWorkbook(in); sheet = hwb.getSheetAt(0); }else{ XSSFWorkbook xwb = xwb = new XSSFWorkbook(in); sheet = xwb.getSheetAt(0); } }catch(Exception e){ e.printStackTrace(); }finally { if(in!=null){ try { in.close(); in = null; } catch (IOException e) { e.printStackTrace(); } } } return sheet; } /** * 读取excel文件,只要某一行的某一列出现空值,直接结束循环,并返回null * @param in * @param fileName * @param titles * @param date * @param length * @return * @throws IOException */ public static List<Map<String,Object>> readExcel(InputStream in,String fileName,String[] titles,String[] colums, Date date,int length){ return readCell(getSheet(in,fileName),titles,colums,date,length); } /** * 读取数据,只要某一行的某一列出现空值,直接结束循环,并返回null * @param sheet * @param titles * @param colums * @param date * @return */ private static List<Map<String,Object>> readCell(Sheet sheet,String[] titles,String[] colums,Date date,int length){ List<Map<String,Object>> res = new ArrayList<>(); int start_row = sheet.getFirstRowNum(); int end_row = sheet.getLastRowNum(); boolean flag = Boolean.TRUE; SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd"); for(int i=start_row; i<=end_row&&flag;i++){//读取行 Row row = sheet.getRow(i); int start_cell = row.getFirstCellNum(); //隐藏下面这这几段是因为 有些行中,是没有数据的,比如一共7列。第一行全有数据,第二行前三个字段有数据,那么此时end_cell=3,就会读取不到完整的数据了@! /*int end_cell = row.getLastCellNum(); if((titles!=null && titles.length<end_cell) || (colums!=null && colums.length<end_cell) || end_cell!=length){ return null; }*/ Map<String,Object> map = new HashMap<>();; for(int j = start_cell; j<titles.length; j++){//读取列 Cell cell = row.getCell(j); /*if(cell==null){ continue; }*/ if(i==0){//检查导入表的标题是否符合colums所给的数据 if(!cell.toString().equals(colums[j])){ res.clear(); res=null; flag = Boolean.FALSE; break; } }else{ Object data = null; if(cell!=null){ //levan增加。处理那些数值为空值,list中key没了的问题 switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数值型 if (HSSFDateUtil.isCellDateFormatted(cell)) { // 如果是date类型则 ,获取该cell的date值 data = format.format(addDay(cell.getDateCellValue(), 0)); } else { //xieyz版本 /*double cellValue = cell.getNumericCellValue(); data = String.valueOf(new DecimalFormat("#").format(cellValue)); */ //Levan版本 cell.setCellType(Cell.CELL_TYPE_STRING); String temp = cell.getStringCellValue(); // 判断是否包含小数点,如果不含小数点,则以字符串读取,如果含小数点,则转换为Double类型的字符串 if (temp.indexOf(".") > -1) { data = String.valueOf(new Double(temp)).trim(); } else { data = temp.trim(); } } break; case HSSFCell.CELL_TYPE_STRING: // 字符串 data = cell.getStringCellValue().replaceAll("\"|'", ""); break; } } map.put(titles[j], data); } } if(date!=null) map.put("createTime", date); if(i!=0) res.add(map); } return res; } private static Date addDay(Date date,int i){ Calendar calendar = Calendar.getInstance(); calendar.setTime(date); calendar.add(Calendar.DAY_OF_MONTH, i); return calendar.getTime(); } public static void main(String[] args) throws IOException{ //woshihd //电渠二季.xls // File f = new File("F:/电渠二季度政企专项活动宽带号码清单.xlsx"); // File f = new File("F:/星级用户首访第二批.xls"); File f = new File("F:/视频类目标用户 (3月流量使用率低于25%)-20170411.xlsx"); } }
相关推荐
-
Logback自定义日志颜色 java
2019-1-7
-
Mybatis+Spring实现Mysql读写分离 java
2019-1-8
-
基于redis实现的分布式锁 java
2019-1-8
-
根据经纬度算距离 java
2019-1-13
-
jgit工具类 java
2019-1-12
-
实现List<Sting>的正则表达式验证 java
2019-1-12
-
Java获取当前系统信息 java
2019-1-12
-
读取a.txt和b.txt中有序的序列,合并有序序列保存成c.txt java
2019-1-13
-
springMVC图片上传,删除 java
2019-1-13
-
RedisShardPoolUtil java
2019-1-13