Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.yyl.common.utils.excel; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.stream.Collectors; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import com.yyl.common.utils.DateUtils; import com.yyl.common.utils.json.JacksonUtil; import com.yyl.common.utils.string.StringUtil; /** * * @author yangyl * @date 2017-1-7 */ public class ExcelTools extends FileTools { public static final String OFFICE_EXCEL_2003_SUFFIX = "xls"; public static final String OFFICE_EXCEL_2010_SUFFIX = "xlsx"; /*--------------------------Read Excel Start------------------------------------------*/ /** * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel??? * @param inputStream ?urlurlinput? * @param FileName ???????excel * @return Map HashMapExcelsheet?sheetkeysheet?json?value * @throws IOException */ public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName) throws IOException { System.out.println("excel2json...."); // map Map<String, String> excelMap = new LinkedHashMap<>(); // Excel??Excel CellStyle cellStyle; // ?Excel? Workbook wb; // 2007??Workbook?CellStyle if (FileName.endsWith("xlsx")) { System.out.println("2007? xlsx"); wb = new XSSFWorkbook(inputStream); XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } else { System.out.println("2007 xls"); POIFSFileSystem fs = new POIFSFileSystem(inputStream); wb = new HSSFWorkbook(fs); HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } // sheet int sheetsCounts = wb.getNumberOfSheets(); // ???sheet for (int i = 0; i < sheetsCounts; i++) { Sheet sheet = wb.getSheetAt(i); System.out.println("" + i + "sheet:" + sheet.toString()); // sheetList List list = new LinkedList(); // jsonkey String[] cellNames; // ?key Row fisrtRow = sheet.getRow(0); // sheet if (null == fisrtRow) { continue; } // int curCellNum = fisrtRow.getLastCellNum(); System.out.println("" + curCellNum); // ??? cellNames = new String[curCellNum]; // ????JSONkey for (int m = 0; m < curCellNum; m++) { Cell cell = fisrtRow.getCell(m); // ? cell.setCellStyle(cellStyle); cell.setCellType(Cell.CELL_TYPE_STRING); // ? cellNames[m] = getCellValue(cell); } for (String s : cellNames) { System.out.print("" + i + " sheet " + s + ","); } System.out.println(); // ??? int rowNum = sheet.getLastRowNum(); System.out.println(" " + rowNum + " "); for (int j = 1; j < rowNum; j++) { // ?Map LinkedHashMap rowMap = new LinkedHashMap(); // ?? Row row = sheet.getRow(j); int cellNum = row.getLastCellNum(); // ??? for (int k = 0; k < cellNum; k++) { Cell cell = row.getCell(k); cell.setCellStyle(cellStyle); cell.setCellType(Cell.CELL_TYPE_STRING); // ??? rowMap.put(cellNames[k], getCellValue(cell)); } // ??List list.add(rowMap); } // sheet??keyListjson?Value excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list)); } System.out.println("excel2json?...."); return excelMap; } /*--------------------------Read Excel Start------------------------------------------*/ /** * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel??? * @param inputStream ?urlurlinput? * @param FileName ???????excel * @param headers list,String-->Arrays.asList(); * @return Map HashMapExcelsheet?sheetkeysheet?json?value * @throws IOException */ public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers) throws IOException { System.out.println("excel2json...."); // map Map<String, String> excelMap = new LinkedHashMap<>(); // Excel??Excel CellStyle cellStyle; // ?Excel? Workbook wb; // 2007??Workbook?CellStyle if (fileName.endsWith("xlsx")) { System.out.println("2007? xlsx"); wb = new XSSFWorkbook(inputStream); XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } else { System.out.println("2007 xls"); POIFSFileSystem fs = new POIFSFileSystem(inputStream); wb = new HSSFWorkbook(fs); HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } // sheet int sheetsCounts = wb.getNumberOfSheets(); // ???sheet for (int i = 0; i < sheetsCounts; i++) { Sheet sheet = wb.getSheetAt(i); System.out.println("" + i + "sheet:" + sheet.toString()); // sheetList List list = new LinkedList(); // ?key Row fisrtRow = sheet.getRow(0); // sheet if (null == fisrtRow) { continue; } // int curCellNum = fisrtRow.getLastCellNum(); System.out.println("" + curCellNum); // ??? int rowNum = sheet.getLastRowNum(); System.out.println(" " + rowNum + " "); for (int j = 1; j < rowNum; j++) { // ?Map LinkedHashMap rowMap = new LinkedHashMap(); // ?? Row row = sheet.getRow(j); int cellNum = row.getLastCellNum(); // ??? for (int k = 0; k < cellNum; k++) { Cell cell = row.getCell(k); // ??? rowMap.put(headers.get(k), getCellValue(cell)); } // ??List list.add(rowMap); } // sheet??keyListjson?Value excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list)); } System.out.println("excel2json?...."); return excelMap; } /** * ?OFFICE Excel??? * @param file * @param originFileName ?? * @param keyMaps Mapkey.<String,Integer>--<???> * @return List<Map<String, String>> * @throws FileException * @throws IOException */ public static List<Map<String, String>> readExcel(InputStream inputStream, String originFileName, Map<String, Integer> keyMaps) throws Exception { if (StringUtil.isBlank(originFileName)) { throw new FileException("path is empty!", FileException.PARAMETER_INVALID_ERROR); } else { String suffix = getSuffix(originFileName); if (OFFICE_EXCEL_2003_SUFFIX.equals(suffix)) { return readXLS(inputStream, keyMaps); } else if (OFFICE_EXCEL_2010_SUFFIX.equals(suffix)) { return readXLSX(inputStream, keyMaps); } else { throw new FileException("it is not a excel file!", FileException.FILE_TYPE_MISMATCHED_ERROR); } } } /** ?Excel?? * * @param file * @param originFileName ?? * @return List<List<Object>> * @throws FileException * @throws IOException */ public static List<List<Object>> readExcelWithHeader(InputStream inputStream, String originFileName) throws FileException, IOException { if (StringUtil.isBlank(originFileName)) { throw new FileException("path is empty!", FileException.PARAMETER_INVALID_ERROR); } else { String suffix = getSuffix(originFileName); if (OFFICE_EXCEL_2003_SUFFIX.equals(suffix)) { return readXLSWithHeader(inputStream); } else if (OFFICE_EXCEL_2010_SUFFIX.equals(suffix)) { return readXLSXWithHeader(inputStream); } else { throw new FileException("it is not a excel file!", FileException.FILE_TYPE_MISMATCHED_ERROR); } } } /** * ?OFFICE Excel??? * @param file * @param originFileName Excel?? * @return List<List<Object>> * @throws FileException * @throws IOException */ public static List<List<Object>> readExcel(InputStream inputStream, String originFileName) throws FileException, IOException { if (StringUtil.isBlank(originFileName)) { throw new FileException("path is empty!", FileException.PARAMETER_INVALID_ERROR); } else { String suffix = getSuffix(originFileName); if (OFFICE_EXCEL_2003_SUFFIX.equals(suffix)) { return readXLS(inputStream); } else if (OFFICE_EXCEL_2010_SUFFIX.equals(suffix)) { return readXLSX(inputStream); } else { throw new FileException("it is not a excel file!", FileException.FILE_TYPE_MISMATCHED_ERROR); } } } /*--------------------------Read Excel End------------------------------------------*/ /*--------------------------Write Excel Start------------------------------------------*/ public static void writeToXLS(ExcelRow heads, ExcelData data, String sheetName, ByteArrayOutputStream out) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet(sheetName); HSSFRow row = sheet1.createRow(0); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell cell; for (int i = 0; i < heads.size(); i++) { cell = row.createCell(i); cell.setCellValue(heads.get(i)); cell.setCellStyle(style); } if (data != null && data.size() != 0) { for (int n = 0; n < data.size(); n++) { row = sheet1.createRow(n + 1); ExcelRow datarow = data.get(n); for (int m = 0; m < datarow.size(); m++) { cell = row.createCell(m); cell.setCellValue(datarow.get(m)); cell.setCellStyle(style); } } } wb.write(out); } /** * ?xlsxExcel * @param heads * @param data ? * @param sheetName Excel? * @param out ? * @return ByteArrayOutputStream * @throws IOException */ public static void writeToXLSX(ExcelRow heads, ExcelData data, String sheetName, ByteArrayOutputStream out) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet(sheetName); XSSFRow row = sheet1.createRow(0); XSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCell cell; for (int i = 0; i < heads.size(); i++) { cell = row.createCell(i); cell.setCellValue(heads.get(i)); cell.setCellStyle(style); } if (data != null && data.size() != 0) { for (int n = 0; n < data.size(); n++) { row = sheet1.createRow(n + 1); ExcelRow datarow = data.get(n); for (int m = 0; m < datarow.size(); m++) { cell = row.createCell(m); cell.setCellValue(datarow.get(m)); cell.setCellStyle(style); } } } wb.write(out); } /*--------------------------Write Excel End------------------------------------------*/ private static List<List<Object>> readXLSWithHeader(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; } for (int row_index = 0; row_index <= sheet.getLastRowNum(); row_index++) { HSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; } public static ResponseEntity<byte[]> export(ByteArrayOutputStream bOutputStream, String fileName) { try { fileName = new String((fileName + DateUtils.formatDate(new Date(), "yyyy-MM-dd") + ".xls").getBytes(), "iso-8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } HttpHeaders headers = new HttpHeaders(); headers.setContentDispositionFormData("attachment", fileName); headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); return new ResponseEntity<byte[]>(bOutputStream.toByteArray(), headers, HttpStatus.CREATED); } private static final String trim(String str) { return str == null ? "" : str.trim(); } private static List<List<Object>> readXLSXWithHeader(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); XSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; } for (int row_index = 0; row_index <= sheet.getLastRowNum(); row_index++) { XSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; } private static List<List<Object>> readXLS(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; } for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) { HSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; } private static List<List<Object>> readXLSX(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); XSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; } for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) { XSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; } private static List<Map<String, String>> readXLS(InputStream inputStream, Map<String, Integer> keyMaps) throws IOException { // InputStream is = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(inputStream); List<Map<String, String>> list = new ArrayList(); Map<String, String> temp = null; for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { HSSFSheet sheet = wb.getSheetAt(sheetIndex); if (sheet == null) { continue; } for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); temp = new HashMap(); if (row != null) { for (Entry<String, Integer> entry : keyMaps.entrySet()) { int index = entry.getValue(); HSSFCell cell = row.getCell(index); temp.put(entry.getKey(), getCellValue(cell)); } } list.add(temp); } } return list; } private static List<Map<String, String>> readXLSX(InputStream inputStream, Map<String, Integer> keyMaps) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); List<Map<String, String>> list = new ArrayList(); Map<String, String> temp = null; for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { XSSFSheet sheet = wb.getSheetAt(sheetIndex); if (sheet == null) { continue; } for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); temp = new HashMap(); if (row != null) { for (Entry<String, Integer> entry : keyMaps.entrySet()) { int index = entry.getValue(); XSSFCell cell = row.getCell(index); temp.put(entry.getKey(), getCellValue(cell)); } } list.add(temp); } } return list; } /** * ?cell * @param cell * @return */ private static String getCellValue(HSSFCell cell) { if (cell == null) { return ""; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { DecimalFormat df = new DecimalFormat("0"); return df.format(cell.getNumericCellValue()); } default: return String.valueOf(cell.getStringCellValue()); } } /** * ?excel * @param cell * @return */ private static String getCellValue(Cell cell) { String cellValue = ""; DecimalFormat df = new DecimalFormat("#"); if (cell == null || cell.equals("") || cell.getCellType() == Cell.CELL_TYPE_BLANK) { System.out.println(cellValue); return cellValue; } switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cellValue = cell.getRichStringCellValue().getString().trim(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellValue = cell.getDateCellValue().toString(); } else { cellValue = df.format(cell.getNumericCellValue()).toString(); } break; case Cell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()).trim(); break; default: cellValue = ""; } return cellValue; } // /** // * excel? key?? // * // * @param file // * @return // * @throws BiffException // * @throws IOException // */ // public static final List<Map<String, String>> readSheet(File file) { // Workbook workbook = null; // List<Map<String, String>> result = null; // try { // workbook = Workbook.getWorkbook(file); // jxl.Sheet sheet = workbook.getSheet(0); // int rowSize = sheet.getRows(); // Cell[] headCell = sheet.getRow(0); // result = new ArrayList<Map<String, String>>(rowSize - 1); // Cell[] cells; // for (int i = 1; i < rowSize; i++) { // cells = sheet.getRow(i); // Map<String, String> data = new HashMap<String, String>(); // for (int j = 0; j < headCell.length; j++) { // if (cells.length > j) { // if (cells[j].getType() == CellType.DATE) { // TODO:? // DateCell dc = (DateCell) cells[j]; // Date date = dc.getDate(); // TimeZone zone = TimeZone.getTimeZone("GMT"); // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // sdf.setTimeZone(zone); // String dateStr = sdf.format(date); // data.put(trim(headCell[j].getContents()), dateStr); // } else if (cells[j].getType() == CellType.NUMBER) { // ? // if (cells[j].getContents().indexOf(".") > 0) { // NumberCell nc = (NumberCell) cells[j]; // double value = nc.getValue(); // data.put(trim(headCell[j].getContents()), trim(String.valueOf(value))); // } else { // data.put(trim(headCell[j].getContents()), trim(cells[j].getContents())); // } // } else { // data.put(trim(headCell[j].getContents()), trim(cells[j].getContents())); // } // // } // } // result.add(data); // } // } catch (BiffException e) { // e.printStackTrace(); // } catch (IOException e) { // e.printStackTrace(); // } catch (Exception e) { // e.printStackTrace(); // } finally { // workbook.close(); // } // // return result; // } public static class ExcelException extends FileTools.FileException { private int row_index = -1; private int col_index = -1; public ExcelException(int row, int col, String ex, int code) { super(ex, code); this.row_index = row; this.col_index = col; } public ExcelException(int row, int col, Throwable ex, int code) { super(ex, code); this.row_index = row; this.col_index = col; } public static final int CELL_DATA_INVALID_ERROR = 75200; public static final int CELL_DATA_FORMAT_ERROR = 75201; public static final int CELL_DATA_MISMATCH_ERROR = 75202; } public static ExcelRow excelHeaders(String... headers) { ExcelRow row = new ExcelRow(); for (int i = 0; i < headers.length; ++i) row.add(headers[i]); return row; } }