Java tutorial
/* * Copyright 2002-2013 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package com.ncc.excel.test; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; 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.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @Title ExcelUtil.java * @Package com.ncc.excel.test * @author * @Email fdtomn@gmail.com * @date 2015-3-16 ?4:09:40 * @Copyright Copyright (c) 2015-2025 * @Company ??? * @version V1.0 */ public class ExcelUtil { //%%%%%%%%-------? ----------%%%%%%%%% /** * ??0 */ private final static int READ_START_POS = 0; /** * ????=0?n */ private final static int READ_END_POS = 0; /** * Excel?0 */ private final static int COMPARE_POS = 0; /** * ??????? */ private final static boolean NEED_COMPARE = true; /** * ?????? */ private final static boolean NEED_OVERWRITE = true; /** * ??sheet */ private final static boolean ONLY_ONE_SHEET = true; /** * ?sheet?ONLY_ONE_SHEET = true */ private final static int SELECTED_SHEET = 0; /** * sheet?0 */ private final static int READ_START_SHEET = 0; /** * ?sheet??=0?n */ private final static int READ_END_SHEET = 0; /** * ???? */ private final static boolean PRINT_MSG = true; //%%%%%%%%-------? ?----------%%%%%%%%% //%%%%%%%%------- ----------%%%%%%%%% /** * Excel */ private String excelPath = "data.xlsx"; /** * ??0 */ private int startReadPos = READ_START_POS; /** * ???0?n */ private int endReadPos = READ_END_POS; /** * ?0 */ private int comparePos = COMPARE_POS; /** * ???true */ private boolean isOverWrite = NEED_OVERWRITE; /** * ??true(??isOverWrite=false) */ private boolean isNeedCompare = NEED_COMPARE; /** * ???sheet */ private boolean onlyReadOneSheet = ONLY_ONE_SHEET; /** * ?sheet */ private int selectedSheetIdx = SELECTED_SHEET; /** * ?sheet?? */ private String selectedSheetName = ""; /** * ?sheet0 */ private int startSheetIdx = READ_START_SHEET; /** * ??sheet0?n */ private int endSheetIdx = READ_END_SHEET; /** * ??? */ private boolean printMsg = PRINT_MSG; //%%%%%%%%------- ?----------%%%%%%%%% public ExcelUtil() { } public ExcelUtil(String excelPath) { this.excelPath = excelPath; } /** * ?new * @return */ public ExcelUtil RestoreSettings() { ExcelUtil instance = new ExcelUtil(this.excelPath); return instance; } /** * ???? * * @Title: writeExcel * @Date : 2014-9-11 ?01:50:38 * @param xlsPath * @throws IOException */ public List<Row> readExcel() throws IOException { return readExcel(this.excelPath); } /** * ???? * * @Title: writeExcel * @Date : 2014-9-11 ?01:50:38 * @param xlsPath * @throws IOException */ public List<Row> readExcel(String xlsPath) throws IOException { //?? if (xlsPath.equals("")) { throw new IOException("??"); } else { File file = new File(xlsPath); if (!file.exists()) { throw new IOException("??"); } } //??? String ext = xlsPath.substring(xlsPath.lastIndexOf(".") + 1); try { if ("xls".equals(ext)) { //xls?? return readExcel_xls(xlsPath); } else if ("xls".equals(ext)) { //xlsx?? return readExcel_xlsx(xlsPath); } else { //??xls?xlsx?? out("?????xls??..."); try { return readExcel_xls(xlsPath); } catch (IOException e1) { out("?xls????xlsx??..."); try { return readExcel_xlsx(xlsPath); } catch (IOException e2) { out("?xls???\n?Excel???"); throw e2; } } } } catch (IOException e) { throw e; } } /** * ??? * * @Title: writeExcel * @Date : 2014-9-11 ?01:50:38 * @param rowList * @throws IOException */ public void writeExcel(List<Row> rowList) throws IOException { writeExcel(rowList, excelPath); } /** * ??? * * @Title: writeExcel * @Date : 2014-9-11 ?01:50:38 * @param rowList * @param xlsPath * @throws IOException */ public void writeExcel(List<Row> rowList, String xlsPath) throws IOException { //?? if (xlsPath.equals("")) { throw new IOException("??"); } //??? String ext = xlsPath.substring(xlsPath.lastIndexOf(".") + 1); try { if ("xls".equals(ext)) { //xls? writeExcel_xls(rowList, xlsPath); } else if ("xls".equals(ext)) { //xlsx? writeExcel_xlsx(rowList, xlsPath); } else { //??xls?xlsx? out("?????xls?..."); try { writeExcel_xls(rowList, xlsPath); } catch (IOException e1) { out("?xls???xlsx??..."); try { writeExcel_xlsx(rowList, xlsPath); } catch (IOException e2) { out("?xls??\n?Excel???"); throw e2; } } } } catch (IOException e) { throw e; } } /** * Excel97-03xls? * * @Title: writeExcel_xls * @Date : 2014-9-11 ?01:50:38 * @param rowList * @param dist_xlsPath * @throws IOException */ public void writeExcel_xls(List<Row> rowList, String dist_xlsPath) throws IOException { writeExcel_xls(rowList, excelPath, dist_xlsPath); } /** * Excel97-03xls? * * @Title: writeExcel_xls * @Date : 2014-9-11 ?01:50:38 * @param rowList * @param src_xlsPath * @param dist_xlsPath * @throws IOException */ public void writeExcel_xls(List<Row> rowList, String src_xlsPath, String dist_xlsPath) throws IOException { // ? if (dist_xlsPath == null || dist_xlsPath.equals("")) { out("?"); throw new IOException("?"); } // ? if (src_xlsPath == null || src_xlsPath.equals("")) { out("?"); throw new IOException("?"); } // ??? if (rowList == null || rowList.size() == 0) { out(""); return; } try { HSSFWorkbook wb = null; // ? File file = new File(dist_xlsPath); if (file.exists()) { // ?? if (isOverWrite) { file.delete(); // ?Excel // wb = new HSSFWorkbook(); // wb.createSheet("Sheet1"); wb = new HSSFWorkbook(new FileInputStream(src_xlsPath)); } else { // ?Excel wb = new HSSFWorkbook(new FileInputStream(file)); } } else { // ?Excel // wb = new HSSFWorkbook(); // wb.createSheet("Sheet1"); wb = new HSSFWorkbook(new FileInputStream(src_xlsPath)); } // rowlistExcel writeExcel(wb, rowList, dist_xlsPath); } catch (IOException e) { e.printStackTrace(); } } /** * Excel97-03xls? * * @Title: writeExcel_xls * @Date : 2014-9-11 ?01:50:38 * @param rowList * @param dist_xlsPath * @throws IOException */ public void writeExcel_xlsx(List<Row> rowList, String dist_xlsPath) throws IOException { writeExcel_xls(rowList, excelPath, dist_xlsPath); } /** * Excel2007xlsx? * * @Title: writeExcel_xlsx * @Date : 2014-9-11 ?01:50:38 * @param rowList * @param xlsPath * @throws IOException */ public void writeExcel_xlsx(List<Row> rowList, String src_xlsPath, String dist_xlsPath) throws IOException { // ? if (dist_xlsPath == null || dist_xlsPath.equals("")) { out("?"); throw new IOException("?"); } // ? if (src_xlsPath == null || src_xlsPath.equals("")) { out("?"); throw new IOException("?"); } // ??? if (rowList == null || rowList.size() == 0) { out(""); return; } try { // ? XSSFWorkbook wb = null; // ? File file = new File(dist_xlsPath); if (file.exists()) { // ?? if (isOverWrite) { file.delete(); // ?Excel // wb = new XSSFWorkbook(); // wb.createSheet("Sheet1"); wb = new XSSFWorkbook(new FileInputStream(src_xlsPath)); } else { // ?Excel wb = new XSSFWorkbook(new FileInputStream(file)); } } else { // ?Excel // wb = new XSSFWorkbook(); // wb.createSheet("Sheet1"); wb = new XSSFWorkbook(new FileInputStream(src_xlsPath)); } // rowlistExcel writeExcel(wb, rowList, dist_xlsPath); } catch (IOException e) { e.printStackTrace(); } } /** * //?Excel 2007xlsx? * * @Title: readExcel_xlsx * @Date : 2014-9-11 ?11:43:11 * @return * @throws IOException */ public List<Row> readExcel_xlsx() throws IOException { return readExcel_xlsx(excelPath); } /** * //?Excel 2007xlsx? * * @Title: readExcel_xlsx * @Date : 2014-9-11 ?11:43:11 * @return * @throws Exception */ public List<Row> readExcel_xlsx(String xlsPath) throws IOException { // ? File file = new File(xlsPath); if (!file.exists()) { throw new IOException("??" + file.getName() + "Excel??"); } XSSFWorkbook wb = null; List<Row> rowList = new ArrayList<Row>(); try { FileInputStream fis = new FileInputStream(file); // Excel wb = new XSSFWorkbook(fis); // ?Excel 2007xlsx? rowList = readExcel(wb); } catch (IOException e) { e.printStackTrace(); } return rowList; } /*** * ?Excel(97-03xls?) * * @throws IOException * * @Title: readExcel * @Date : 2014-9-11 ?09:53:21 */ public List<Row> readExcel_xls() throws IOException { return readExcel_xls(excelPath); } /*** * ?Excel(97-03xls?) * * @throws Exception * * @Title: readExcel * @Date : 2014-9-11 ?09:53:21 */ public List<Row> readExcel_xls(String xlsPath) throws IOException { // ? File file = new File(xlsPath); if (!file.exists()) { throw new IOException("??" + file.getName() + "Excel??"); } HSSFWorkbook wb = null;// Workbook??Excel List<Row> rowList = new ArrayList<Row>(); try { // ?Excel wb = new HSSFWorkbook(new FileInputStream(file)); // ?Excel 97-03xls? rowList = readExcel(wb); } catch (IOException e) { e.printStackTrace(); } return rowList; } /*** * ?? * * @Title: getCellValue * @Date : 2014-9-11 ?10:52:07 * @param cell * @return */ private String getCellValue(Cell cell) { Object result = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: result = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: result = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_BLANK: break; default: break; } } return result.toString(); } /** * ?Excel * * @Title: readExcel * @Date : 2014-9-11 ?11:26:53 * @param wb * @return */ private List<Row> readExcel(Workbook wb) { List<Row> rowList = new ArrayList<Row>(); int sheetCount = 1;//??sheet? Sheet sheet = null; if (onlyReadOneSheet) { //??sheet // ??sheet(?????) sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName); } else { //?sheet sheetCount = wb.getNumberOfSheets();//???? } // ?sheet for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) { // ??sheet if (!onlyReadOneSheet) { sheet = wb.getSheetAt(t); } //??? int lastRowNum = sheet.getLastRowNum(); if (lastRowNum > 0) { //>0? out("\n????" + sheet.getSheetName() + ""); } Row row = null; // ? for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) { row = sheet.getRow(i); if (row != null) { rowList.add(row); out("" + (i + 1) + "", false); // ??? for (int j = 0; j < row.getLastCellNum(); j++) { String value = getCellValue(row.getCell(j)); if (!value.equals("")) { out(value + " | ", false); } } out(""); } } } return rowList; } /** * Excel? * * @Title: WriteExcel * @Date : 2014-9-11 ?01:33:59 * @param wb * @param rowList * @param xlsPath */ private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) { if (wb == null) { out("???"); return; } Sheet sheet = wb.getSheetAt(0);// sheet // ??????? int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1; int t = 0;// out("???" + rowList.size()); for (Row row : rowList) { if (row == null) continue; // ??? int pos = findInExcel(sheet, row); Row r = null;// ?????? if (pos >= 0) { sheet.removeRow(sheet.getRow(pos)); r = sheet.createRow(pos); } else { r = sheet.createRow(lastRowNum + t++); } //?? CellStyle newstyle = wb.createCellStyle(); //? for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { Cell cell = r.createCell(i);// ?? cell.setCellValue(getCellValue(row.getCell(i)));// ??? // cell.setCellStyle(row.getCell(i).getCellStyle());// if (row.getCell(i) == null) continue; copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // ???? cell.setCellStyle(newstyle);// ? // sheet.autoSizeColumn(i);// } } out("???:" + (rowList.size() - t) + " ?" + t); // ?? setMergedRegion(sheet); try { // ??Excel FileOutputStream outputStream = new FileOutputStream(xlsPath); wb.write(outputStream); outputStream.flush(); outputStream.close(); } catch (Exception e) { out("Excel?? "); e.printStackTrace(); } } /** * ???Excel * * @Title: findInExcel * @Date : 2014-9-11 ?02:23:12 * @param sheet * @param row * @return */ private int findInExcel(Sheet sheet, Row row) { int pos = -1; try { // ?? if (isOverWrite || !isNeedCompare) { return pos; } for (int i = startReadPos; i <= sheet.getLastRowNum() + endReadPos; i++) { Row r = sheet.getRow(i); if (r != null && row != null) { String v1 = getCellValue(r.getCell(comparePos)); String v2 = getCellValue(row.getCell(comparePos)); if (v1.equals(v2)) { pos = i; break; } } } } catch (Exception e) { e.printStackTrace(); } return pos; } /** * ????? * * @param fromStyle * @param toStyle */ public static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle) { toStyle.setAlignment(fromStyle.getAlignment()); // toStyle.setBorderBottom(fromStyle.getBorderBottom()); toStyle.setBorderLeft(fromStyle.getBorderLeft()); toStyle.setBorderRight(fromStyle.getBorderRight()); toStyle.setBorderTop(fromStyle.getBorderTop()); toStyle.setTopBorderColor(fromStyle.getTopBorderColor()); toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor()); toStyle.setRightBorderColor(fromStyle.getRightBorderColor()); toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor()); // ? toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor()); toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor()); // ?? toStyle.setDataFormat(fromStyle.getDataFormat()); toStyle.setFillPattern(fromStyle.getFillPattern()); // toStyle.setFont(fromStyle.getFont(null)); toStyle.setHidden(fromStyle.getHidden()); toStyle.setIndention(fromStyle.getIndention());// toStyle.setLocked(fromStyle.getLocked()); toStyle.setRotation(fromStyle.getRotation());// toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment()); toStyle.setWrapText(fromStyle.getWrapText()); } /** * ??? * * @param sheet * @param row * @param column * @return */ public void setMergedRegion(Sheet sheet) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { // ???? CellRangeAddress ca = sheet.getMergedRegion(i); int firstRow = ca.getFirstRow(); if (startReadPos - 1 > firstRow) {// ?????? continue; } int lastRow = ca.getLastRow(); int mergeRows = lastRow - firstRow;// ? int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); // ??????? for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) { // ?? sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn)); j = j + mergeRows;// ? } } } /** * ?? * @param msg ? * @param tr ? */ private void out(String msg) { if (printMsg) { out(msg, true); } } /** * ?? * @param msg ? * @param tr ? */ private void out(String msg, boolean tr) { if (printMsg) { System.out.print(msg + (tr ? "\n" : "")); } } public String getExcelPath() { return this.excelPath; } public void setExcelPath(String excelPath) { this.excelPath = excelPath; } public boolean isNeedCompare() { return isNeedCompare; } public void setNeedCompare(boolean isNeedCompare) { this.isNeedCompare = isNeedCompare; } public int getComparePos() { return comparePos; } public void setComparePos(int comparePos) { this.comparePos = comparePos; } public int getStartReadPos() { return startReadPos; } public void setStartReadPos(int startReadPos) { this.startReadPos = startReadPos; } public int getEndReadPos() { return endReadPos; } public void setEndReadPos(int endReadPos) { this.endReadPos = endReadPos; } public boolean isOverWrite() { return isOverWrite; } public void setOverWrite(boolean isOverWrite) { this.isOverWrite = isOverWrite; } public boolean isOnlyReadOneSheet() { return onlyReadOneSheet; } public void setOnlyReadOneSheet(boolean onlyReadOneSheet) { this.onlyReadOneSheet = onlyReadOneSheet; } public int getSelectedSheetIdx() { return selectedSheetIdx; } public void setSelectedSheetIdx(int selectedSheetIdx) { this.selectedSheetIdx = selectedSheetIdx; } public String getSelectedSheetName() { return selectedSheetName; } public void setSelectedSheetName(String selectedSheetName) { this.selectedSheetName = selectedSheetName; } public int getStartSheetIdx() { return startSheetIdx; } public void setStartSheetIdx(int startSheetIdx) { this.startSheetIdx = startSheetIdx; } public int getEndSheetIdx() { return endSheetIdx; } public void setEndSheetIdx(int endSheetIdx) { this.endSheetIdx = endSheetIdx; } public boolean isPrintMsg() { return printMsg; } public void setPrintMsg(boolean printMsg) { this.printMsg = printMsg; } public static void main(String[] args) throws IOException { ExcelUtil eu = new ExcelUtil(); String excelPath = "D:/poi/2003.xls"; eu.setExcelPath(excelPath); eu.readExcel(); } }