读取excel工具utils
读取excel工具utils
import org.apache.commons.fileupload.FileItem; import org.apache.log4j.LogManager; import org.apache.log4j.Logger; import com.hz.common.excel.Excel2003Reader; import com.hz.common.excel.Excel2007Reader; import com.hz.common.excel.IRowReader; import java.io.FileInputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.List; public final class ExcelReaderUtil { private ExcelReaderUtil() { } private static final Logger logger = LogManager.getLogger(ExcelReaderUtil.class); // excel2003扩展名 public static final String EXCEL03_EXTENSION = ".xls"; // excel2007扩展名 public static final String EXCEL07_EXTENSION = ".xlsx"; /** * 读取Excel文件，可能是03也可能是07版本 * * @param reader * @param fileName * @throws Exception */ public static void readExcel(IRowReader reader, String fileName) throws Exception { // 处理excel2003文件 if (fileName.endsWith(EXCEL03_EXTENSION)) { Excel2003Reader excel03 = new Excel2003Reader(reader); excel03.process(fileName); // 处理excel2007文件 } else if (fileName.endsWith(EXCEL07_EXTENSION)) { Excel2007Reader excel07 = new Excel2007Reader(reader); excel07.process(fileName); } else { throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。"); } } /** * 读取Excel文件，可能是03也可能是07版本 * * @param reader * @param fileName * @param inputStream * @throws Exception */ public static void readExcel(IRowReader reader, String fileName, InputStream inputStream) throws Exception { // 处理excel2003文件 if (fileName.endsWith(EXCEL03_EXTENSION)) { Excel2003Reader excel03 = new Excel2003Reader(reader); excel03.process(inputStream); // 处理excel2007文件 } else if (fileName.endsWith(EXCEL07_EXTENSION)) { Excel2007Reader excel07 = new Excel2007Reader(reader); excel07.process(inputStream); } else { throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。"); } } /** * 获取Excel数据,返回List<String[]> * * @param sheetNumber * 读取工作表的下标(从1开始).可有可无,默认读取所有表单. */ public static final List<String[]> excelToArrayList(String filePath, int... sheetNumber) throws Exception { List<String[]> resultList = null; InputStream is = null; try { is = new FileInputStream(filePath); resultList = excelToArrayList(filePath, is, sheetNumber); } catch (Exception e) { throw e; } finally { if (is != null) { is.close(); } } return resultList; } /** * 获取Excel数据,返回List<String[]> * * @param sheetNumber * 读取工作表的下标(从1开始).可有可无,默认读取所有表单. */ public static final List<String[]> excelToArrayList(String fileName, FileItem fileItem, int... sheetNumber) throws Exception { List<String[]> resultList = null; InputStream is = null; try { is = fileItem.getInputStream(); resultList = excelToArrayList(fileName, is, sheetNumber); } catch (Exception e) { throw e; } finally { if (is != null) { is.close(); } } return resultList; } /** * 获取Excel数据,返回List<String[]>; * * @param sheetNumber * 读取工作表的下标(从1开始).可有可无,默认读取所有表单. */ public static final List<String[]> excelToArrayList(String fileName, InputStream is, final int... sheetNumber) throws Exception { final ArrayList<String[]> resultList = new ArrayList<String[]>(); try { readExcel(new IRowReader() { public void getRows(int sheetIndex, int curRow, List<String> rowlist) { if (sheetNumber == null) { resultList.add(rowlist.toArray(new String[] {})); } else { for (int k = 0; k < sheetNumber.length; k++) {// 循环工作表 if (sheetIndex == sheetNumber[k]) { resultList.add(rowlist.toArray(new String[] {})); } } } } }, fileName, is); } catch (Exception e) { logger.error("Read excel error.", e); throw e; } return resultList; } }
Excel 公共方法 utils
/** * Excel 公共方法 * */ public class ExcelUtil { public static List<Object> list = new ArrayList<Object>(); /** * 导出多个工作表(sheet)的excel Think.zf * * @param title * excel标题 * @param mapColumnNames * sheet映射数据列表,key:sheet名称 value:该工作表需要显示的列名 * 列名 * @param mapDataList * sheet映射数据列表,key:sheet名称 value:该工作表数据列表 * 显示数据 列明对应含有img的代表从服务端读取图片，之后请自行忽略img * @param response * @param request * @throws Exception */ public static void exportManySheet(String title, Map<String,String[]> mapColumnNames, Map<String,List<Map<String, Object>>> mapDataList, HttpServletResponse response, HttpServletRequest request) throws Exception { try { HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象 String sheetName = "";//工作表名称 String[] columnNames = {};//工作表数据列名 List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();//工作表数据信息 for (Map.Entry<String, String[]> entryColumnMap: mapColumnNames.entrySet()) { sheetName = entryColumnMap.getKey(); columnNames = entryColumnMap.getValue(); dataList = mapDataList.get(sheetName); HSSFSheet sheet = workbook.createSheet(sheetName); // 创建工作表 // 产生表格标题行 HSSFRow rowm = sheet.createRow(0); HSSFCell cellTiltle = rowm.createCell(0); // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】 HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);// 获取列头样式对象 HSSFCellStyle style = getStyle(workbook); // 单元格样式对象 // 设置标题 sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (columnNames.length - 1))); cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(title); // 将每列的列名放进sheet中 int columnNum = insertColumnNames(columnNames, sheet, columnTopStyle); // 将数据放进sheet中 insertCellValue(dataList, request, workbook, sheet, style); // 让列宽随着导出的列长自动适应数据长度 formatExcelCellWdith(sheet, columnNum); } // 将数据信息workbook 导出excel文件 exportExcel(response, workbook); } catch (Exception e) { e.printStackTrace(); } } /** * 导出只有一个工作表(sheet)的excel Think.zf * * @param title * excel标题 * @param columnNames * 列明 * @param dataList * 显示数据 列明对应含有img的代表从服务端读取图片，之后请自行忽略img * @param response * @param request * @throws Exception */ public static void export(String title, String[] columnNames, List<Map<String, Object>> dataList, HttpServletResponse response, HttpServletRequest request) throws Exception { try { HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象 HSSFSheet sheet = workbook.createSheet(title); // 创建工作表 // 产生表格标题行 HSSFRow rowm = sheet.createRow(0); HSSFCell cellTiltle = rowm.createCell(0); // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】 HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);// 获取列头样式对象 HSSFCellStyle style = getStyle(workbook); // 单元格样式对象 // 设置标题 sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (columnNames.length - 1))); cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(title); // 将每列的列名放进sheet中 int columnNum = insertColumnNames(columnNames, sheet, columnTopStyle); // 将数据放进sheet中 insertCellValue(dataList, request, workbook, sheet, style); // 让列宽随着导出的列长自动适应数据长度 formatExcelCellWdith(sheet, columnNum); // 将数据信息workbook 导出excel文件 exportExcel(response, workbook); } catch (Exception e) { e.printStackTrace(); } } /** * 插入展示列明 * * @param columnNames * @param sheet * @param columnTopStyle */ private static int insertColumnNames(String[] columnNames, HSSFSheet sheet, HSSFCellStyle columnTopStyle) { // 定义所需列数 int columnNum = columnNames.length; HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行) // 将列头设置到sheet的单元格中 for (int n = 0; n < columnNum; n++) { HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型 HSSFRichTextString text = new HSSFRichTextString(columnNames[n]); cellRowName.setCellValue(text); // 设置列头单元格的值 cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式 } return columnNum; } /** * 导出excel文件 Think.zf * * @param response * @param workbook * 2017-06-08 */ private static void exportExcel(HttpServletResponse response, HSSFWorkbook workbook) { if (workbook != null) { try { String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls"; String headStr = "attachment; filename=\"" + fileName + "\""; response.setContentType("APPLICATION/OCTET-STREAM"); response.setHeader("Content-Disposition", headStr); ServletOutputStream out = response.getOutputStream(); workbook.write(out); } catch (Exception e) { e.printStackTrace(); } } } /** * 插入数据 * * @param dataList * 导出数据 * @param request * 主要获取web项目路径 * @param workbook * @param sheet * @param style * @throws Exception */ private static void insertCellValue(List<Map<String, Object>> dataList, HttpServletRequest request, HSSFWorkbook workbook, HSSFSheet sheet, HSSFCellStyle style) throws Exception { // 将查询出的数据设置到sheet对应的单元格中 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); for (int i = 0; i < dataList.size(); i++) { Map<String, Object> rowMap = dataList.get(i);// 遍历每个对象 HSSFRow row = sheet.createRow(i + 3);// 创建所需的行数 row.setHeight((short) 2000);// 设置150行高度 sheet.setColumnWidth(0, 16*256);//设置第一列的宽度 //sheet.setColumnWidth(1, 20*256);//设置第二列的宽度 int j = 0; int rowNum = row.getRowNum(); for (Map.Entry<String, Object> entry : rowMap.entrySet()) { HSSFCell cell = null; // 设置单元格的数据类型 if (entry.getKey().trim().indexOf("img") != -1) { // 图片 String imgFilePath = entry.getValue().toString();// 服务端图片地址 String serverBasePath = request.getSession().getServletContext().getRealPath("/");// 获取服务端上下文路径 byte[] imgOutputStreamBytes = null;//图片输出流 String _filePath = (new StringBuilder().append(imgFilePath)).toString(); if(_filePath.indexOf("https") != -1){ //包含https得，用https获取文件流 imgOutputStreamBytes = ImageUtil.getOutputStreamByHttpsImgUrl(_filePath); }else{ //一般网络路径地址,这个路径没有http，需要添加http if(_filePath.indexOf("http")!=-1){ imgOutputStreamBytes = ImageUtil.getOutputStreamByHttpImgUrl(_filePath); }else{ imgOutputStreamBytes = ImageUtil.getOutputStreamByHttpImgUrl(String.format("%s%s", Constants.IMG_PRE,_filePath)); } } // URL urlfile = new URL(_filePath); //读取远程服务器图片 // File imgFile = new File(_filePath);//读取本地图片 cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING); cell.setCellValue("图片"); if(imgOutputStreamBytes != null) { //bufferImg = ImageIO.read(imgFile); // ImageIO.write(bufferImg, "png", byteArrayOut); HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, (short) (j), rowNum, (short) (j + 1), rowNum + 1); patriarch.createPicture(anchor, workbook.addPicture(imgOutputStreamBytes, HSSFWorkbook.PICTURE_TYPE_JPEG)); } } else { cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING); if (null != entry.getValue()) cell.setCellValue(new HSSFRichTextString(entry.getValue().toString())); else cell.setCellValue(""); } cell.setCellStyle(style); j++; } } } private static Boolean urlFileIsExsits(URL urlfile) throws IOException { Boolean isExists; HttpURLConnection urlcon2 = (HttpURLConnection) urlfile.openConnection(); String message = urlcon2.getHeaderField(0); if (!message.startsWith("HTTP/1.1 404")) { isExists = true; }else{ isExists = false; } return isExists; } /** * 格式化excel列的宽度,自适应 * * @param sheet * @param columnNum */ private static void formatExcelCellWdith(HSSFSheet sheet, int columnNum) { for (int colNum = 0; colNum < columnNum; colNum++) { int columnWidth = sheet.getColumnWidth(colNum) / 256; for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { HSSFRow currentRow; // 当前行未被使用过 if (sheet.getRow(rowNum) == null) { currentRow = sheet.createRow(rowNum); } else { currentRow = sheet.getRow(rowNum); } if (currentRow.getCell(colNum) != null) { HSSFCell currentCell = currentRow.getCell(colNum); if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String currentCellValue = currentCell.getStringCellValue();// 获取当前列的值 int length = 0; for (String cValue : currentCellValue.split("\r\n")) { if (length < cValue.getBytes().length) length = cValue.getBytes().length; } if (columnWidth < length) { columnWidth = length; } } } } if (colNum == 0) { sheet.setColumnWidth(colNum, (columnWidth - 2) * 256); } else { sheet.setColumnWidth(colNum, (columnWidth + 4) * 256); } } } /* * 列头单元格样式 */ private static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); // 设置字体大小 font.setFontHeightInPoints((short) 11); // 字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置字体名字 font.setFontName("Courier New"); // 设置样式; HSSFCellStyle style = workbook.createCellStyle(); // 设置底边框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置左边框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); // 设置右边框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); // 设置顶边框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); // 在样式用应用设置的字体; style.setFont(font); // 设置自动换行; style.setWrapText(false); // 设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } /* * 列数据信息单元格样式 */ private static HSSFCellStyle getStyle(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); // 设置字体大小 // font.setFontHeightInPoints((short)10); // 字体加粗 // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置字体名字 font.setFontName("Courier New"); // 设置样式; HSSFCellStyle style = workbook.createCellStyle(); // 设置底边框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置左边框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); // 设置右边框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); // 设置顶边框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); // 在样式用应用设置的字体; style.setFont(font); // 设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 使用\r\n 可换行 style.setWrapText(true); return style; } }
