/** * 使用poi报表导出工具类 把poi的一个调用接口抽出来,便于导出功能的管理 */ public class ExcelUtil { /** * 导出list中map做载体的数据到excel 参数说明: list:存放了Map数据的集合 hdNames:表头列名 hds:对应表头的数据KEY xlsName:导出文件名 */ public static <T> boolean outPutExcelByMap(List<Map<String, Object>> list, String[] hdNames,String[] hds, String xlsName, HttpServletRequest request, HttpServletResponse response) throws Exception { Workbook wb = new HSSFWorkbook(); // 创建工作薄 Sheet sheet = wb.createSheet(); // 创建工作表 sheet.autoSizeColumn(( short ) 0 ); // 自适应宽度 // 写入表头---Excel的第一行数据 Row nRow = sheet.createRow( 0 ); // 创建行 for ( int i = 0 ; i < hdNames.length; i++) { Cell nCell = nRow.createCell(i); // 创建单元格 nCell.setCellValue(hdNames[i]); } // 写入每一行数据---一条记录就是一行数据 for ( int i = 0 ; i < list.size(); i++) { for ( int j = 0 ; j < hds.length; j++) { Object o = list.get(i).get(hds[j]); // 得到列的值 data2Excel(sheet, o, i + 1 , j); // 将值写入Excel } } setSizeColumn(sheet, hdNames.length); return downloadExcel(wb, xlsName, request, response); } /** * Excels导出多个sheet */ // map:data,sheetName,hds,hdNames,xlsName;request,response @SuppressWarnings ( "rawtypes" ) public static boolean outPutExcels(List<Map<String, Object>> list, String xlsName, HttpServletRequest request, HttpServletResponse response) throws Exception { Workbook wb = new HSSFWorkbook(); // 创建工作薄 for ( int i = 0 ; i < list.size(); i++) { Map<String, Object> map = list.get(i); Sheet sheet = wb.createSheet(); // 创建工作表 String sheetName = (String)map.get( "sheetName" ); wb.setSheetName(i, sheetName); // 写入表头---Excel的第一行数据 Row nRow = sheet.createRow( 0 ); // 创建行 String[] hdNames = (String[])map.get( "hdNames" ); for ( int j = 0 ; j < hdNames.length; j++) { Cell nCell = nRow.createCell(j); // 创建单元格 nCell.setCellValue(hdNames[j]); } // 写入每一行数据---一条记录就是一行数据 @SuppressWarnings ( "unchecked" ) List<? extends Model> data = (List<? extends Model>)map.get( "data" ); String[] hds = (String[])map.get( "hds" ); for ( int j = 0 ; j < data.size(); j++) { for ( int k = 0 ; k < hds.length; k++) { Object o = getFieldValue(data.get(j), hds[k]); // 得到列的值 data2Excel(sheet, o, j + 1 , k); // 将值写入Excel } } } return downloadExcel(wb, xlsName, request, response); } /** * 参数说明: list:list数据集合 hdNames:表头需要显示的名称 * hds:表头对应的对象属性名称,和 hdNames一一对应 xlsName:导出Excel的预定义名称 * request: * HttpServletRequest response:HttpServletResponse 通过数据构建Excel * * @throws Exception */ @SuppressWarnings ( "rawtypes" ) public static <T> boolean outPutExcel(List<? extends Model>list,String[] hdNames, String[] hds, String xlsName, HttpServletRequest request, HttpServletResponse response) throws Exception { Workbook wb = new HSSFWorkbook(); // 创建工作薄 Sheet sheet = wb.createSheet(); // 创建工作表 sheet.autoSizeColumn(( short ) 0 ); // 自适应宽度 // 写入表头---Excel的第一行数据 Row nRow = sheet.createRow( 0 ); // 创建行 for ( int i = 0 ; i < hdNames.length; i++) { Cell nCell = nRow.createCell(i); // 创建单元格 nCell.setCellValue(hdNames[i]); } // 写入每一行数据---一条记录就是一行数据 for ( int i = 0 ; i < list.size(); i++) { for ( int j = 0 ; j < hds.length; j++) { Object o = getFieldValue(list.get(i), hds[j]); // 得到列的值 data2Excel(sheet, o, i + 1 , j); // 将值写入Excel } } return downloadExcel(wb, xlsName, request, response); } /** * 传递一个Wookbook,给定文件名,以及request和response下载Excel文档 * * @throws IOException */ @SuppressWarnings ( "all" ) private static boolean downloadExcel(Workbook wb, String xlsName, HttpServletRequest request, HttpServletResponse response) throws IOException { if (request.getHeader( "user-agent" ).indexOf( "MSIE" ) != - 1 ) { xlsName = java.net.URLEncoder.encode(xlsName, "utf-8" ) + ".xls" ; } else { xlsName = new String(xlsName.getBytes( "utf-8" ), "iso-8859-1" ) + ".xls" ; } OutputStream os = response.getOutputStream(); response.setContentType( "application/vnd.ms-excel" ); response.setHeader( "Content-disposition" , "attachment;filename=" + xlsName); wb.write(os); return true ; } /** * 通过泛型实例对象得到某一字段值 */ @SuppressWarnings ( "rawtypes" ) private static Object getFieldValue(Model m, String fieldName) throws Exception { if (m == null ) { return null ; } return m.get(fieldName); } /** * 将数据写到Excel中 */ private static void data2Excel(Sheet sheet, Object o, Integer r, Integer c) { // 通过获得sheet中的某一列,有得到,没有创建 Row nRow = sheet.getRow(r); if (nRow == null ) { nRow = sheet.createRow(r); } // nRow.setColumnWidth(r, arg1); Cell nCell = nRow.createCell(c); // 根据不同类型进行转化,如有其它类型没有考虑周全的,使用发现的时候添加 char type = 'x' ; if (o instanceof Integer) { type = 1 ; } else if (o instanceof Double) { type = 2 ; } else if (o instanceof Float) { type = 3 ; } else if (o instanceof String) { type = 4 ; } else if (o instanceof Date) { type = 5 ; } else if (o instanceof Calendar) { type = 6 ; } else if (o instanceof Boolean) { type = 7 ; } else if (o == null ) { type = 8 ; } switch (type) { case 1 : nCell.setCellValue((Integer)o); break ; case 2 : nCell.setCellValue((Double)o); break ; case 3 : nCell.setCellValue((Float)o); break ; case 4 : nCell.setCellValue((String)o); break ; case 5 : nCell.setCellValue( new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ).format(o)); break ; case 6 : nCell.setCellValue((Calendar)o); break ; case 7 : nCell.setCellValue((Boolean)o); break ; case 8 : nCell.setCellValue( "" ); break ; default : nCell.setCellValue(o + "" ); break ; } } public static List<String[]> getExcelData(File file) { return getData(file, 0 ).get( 0 ); // 选择sheet1 } public static List<String[]> getExcelData(File file, int rowStart) { return getData(file, rowStart).get( 0 ); // 选择sheet1 } public static List<List<String[]>> getData(File file, int rowStart) { HSSFWorkbook workbook; List<List<String[]>> data = new ArrayList<List<String[]>>(); try { workbook = new HSSFWorkbook( new FileInputStream(file)); HSSFSheet sheet = null ; // 循环sheet for ( int i = 0 ; i < workbook.getNumberOfSheets(); i++) { sheet = workbook.getSheetAt(i); List<String[]> rows = new ArrayList<String[]>(); int colsnum = 0 ; // 循环每一行 for ( int j = rowStart; j <= sheet.getLastRowNum(); j++) { HSSFRow row = sheet.getRow(j); if ( null != row) { // 列数以excel第二行为准,第二行为标题,第一行为excel导入提示信息 colsnum = sheet.getRow( 1 ).getLastCellNum(); String[] cols = new String[colsnum]; // 循环每一个单元格,以一行为单位,组成一个数组 for ( int k = 0 ; k < colsnum; k++) { // 判断单元格是否为null,若为null,则置空 if ( null != row.getCell(k)) { int type = row.getCell(k).getCellType(); // 判断单元格数据是否为数字 if (type == HSSFCell.CELL_TYPE_NUMERIC) { // 判断该数字的计数方法是否为科学计数法,若是,则转化为普通计数法 if (String.valueOf(row.getCell(k).getNumericCellValue()) .matches( ".*[E|e].*" )) { DecimalFormat df = new DecimalFormat( "#.#" ); // 指定最长的小数点位为10 df.setMaximumFractionDigits( 10 ); cols[k] = df.format(row.getCell(k).getNumericCellValue()); // 判断该数字是否是日期,若是则转成字符串 } else if (HSSFDateUtil.isCellDateFormatted(row.getCell(k))) { Date d = row.getCell(k).getDateCellValue(); DateFormat formater = new SimpleDateFormat( "yyyy-MM-dd" ); cols[k] = formater.format(d); } else { cols[k] = (row.getCell(k) + "" ).trim(); } } else { cols[k] = (row.getCell(k) + "" ).trim(); } } else { cols[k] = "" ; } } // 以一行为单位,加入list rows.add(cols); } } // 返回所有数据,第一个list表示sheet, //第二个list表示sheet内所有行数据,第三个string[]表示单元格数据 data.add(rows); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return data; } private static void setSizeColumn(Sheet sheet, int size) { for ( int columnNum = 0 ; columnNum < size; columnNum++) { sheet.autoSizeColumn(( short )columnNum); int columnWidth = sheet.getColumnWidth(columnNum) / 256 ; for ( int rowNum = 0 ; rowNum < sheet.getLastRowNum(); rowNum++) { Row currentRow; // 当前行未被使用过 if (sheet.getRow(rowNum) == null ) { currentRow = sheet.createRow(rowNum); } else { currentRow = sheet.getRow(rowNum); } if (currentRow.getCell(columnNum) != null ) { Cell currentCell = currentRow.getCell(columnNum); if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { int length = currentCell.getStringCellValue().getBytes().length; if (columnWidth < length) { columnWidth = length; } } } } sheet.setColumnWidth(columnNum, 256 * columnWidth + 184 ); } } } |