Java tutorial
/* * * Copyright 2017 Crab2Died * All rights reserved. * * Licensed to the Apache Software Foundation (ASF) under one or more * contributor license agreements. See the NOTICE file distributed with * this work for additional information regarding copyright ownership. * The ASF licenses this file to You 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. * * Browse for more information * 1) https://gitee.com/Crab2Died/Excel4J * 2) https://github.com/Crab2died/Excel4J * */ package com.github.crab2died.handler; import com.github.crab2died.exceptions.Excel4JException; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.Calendar; import java.util.Date; import java.util.Map; public class SheetTemplateHandler { // SheetTemplate public static SheetTemplate sheetTemplateBuilder(String templatePath) throws Excel4JException { SheetTemplate sheetTemplate = new SheetTemplate(); try { // ?? sheetTemplate.workbook = WorkbookFactory.create(new FileInputStream(new File(templatePath))); } catch (Exception e) { // ?? try { sheetTemplate.workbook = WorkbookFactory .create(SheetTemplateHandler.class.getResourceAsStream(templatePath)); } catch (IOException | InvalidFormatException e1) { throw new Excel4JException(e1); } } return sheetTemplate; } public static SheetTemplate sheetTemplateBuilder(InputStream is) throws Excel4JException { SheetTemplate sheetTemplate = new SheetTemplate(); // ?? try { sheetTemplate.workbook = WorkbookFactory.create(is); } catch (IOException | InvalidFormatException e) { throw new Excel4JException(e); } return sheetTemplate; } /*-----------------------------------??-----------------------------------*/ public static void loadTemplate(SheetTemplate template, int sheetIndex) { if (sheetIndex < 0) sheetIndex = 0; template.sheetIndex = sheetIndex; template.sheet = template.workbook.getSheetAt(sheetIndex); initModuleConfig(template); template.currentRowIndex = template.initRowIndex; template.currentColumnIndex = template.initColumnIndex; template.lastRowIndex = template.sheet.getLastRowNum(); } /** * ??? */ private static void initModuleConfig(SheetTemplate template) { for (Row row : template.sheet) { for (Cell c : row) { if (c.getCellTypeEnum() != CellType.STRING) continue; String str = c.getStringCellValue().trim().toLowerCase(); // ?? if (HandlerConstant.SERIAL_NUMBER.equals(str)) { template.serialNumberColumnIndex = c.getColumnIndex(); } // ? if (HandlerConstant.DATA_INIT_INDEX.equals(str)) { template.initColumnIndex = c.getColumnIndex(); template.initRowIndex = row.getRowNum(); template.rowHeight = row.getHeightInPoints(); } // ??? initStyles(template, c, str); } } } /** * ??? */ private static void initStyles(SheetTemplate template, Cell cell, String moduleContext) { if (null == moduleContext || "".equals(moduleContext)) return; if (!moduleContext.startsWith("&")) moduleContext = moduleContext.toLowerCase(); if (HandlerConstant.DEFAULT_STYLE.equals(moduleContext)) { template.defaultStyle = cell.getCellStyle(); clearCell(cell); } if (moduleContext.startsWith("&") && moduleContext.length() > 1) { template.classifyStyle.put(moduleContext.substring(1), cell.getCellStyle()); clearCell(cell); } if (HandlerConstant.APPOINT_LINE_STYLE.equals(moduleContext)) { template.appointLineStyle.put(cell.getRowIndex(), cell.getCellStyle()); clearCell(cell); } if (HandlerConstant.SINGLE_LINE_STYLE.equals(moduleContext)) { template.singleLineStyle = cell.getCellStyle(); clearCell(cell); } if (HandlerConstant.DOUBLE_LINE_STYLE.equals(moduleContext)) { template.doubleLineStyle = cell.getCellStyle(); clearCell(cell); } } private static void clearCell(Cell cell) { cell.setCellStyle(null); cell.setCellValue(""); } /*-----------------------------------???-----------------------------------*/ /*-----------------------------------?------------------------------------*/ /** * ?map??Map??# * * @param data ? */ public static void extendData(SheetTemplate template, Map<String, String> data) { if (data == null) return; for (Row row : template.sheet) { for (Cell c : row) { if (c.getCellTypeEnum() != CellType.STRING) continue; String str = c.getStringCellValue().trim(); if (str.startsWith("#") && data.containsKey(str.substring(1))) { c.setCellValue(data.get(str.substring(1))); } } } } /** * ??? */ public static void createNewRow(SheetTemplate template) { if (template.lastRowIndex > template.currentRowIndex && template.currentRowIndex != template.initRowIndex) { template.sheet.shiftRows(template.currentRowIndex, template.lastRowIndex, 1, true, true); template.lastRowIndex++; } template.currentRow = template.sheet.createRow(template.currentRowIndex); template.currentRow.setHeightInPoints(template.rowHeight); template.currentRowIndex++; template.currentColumnIndex = template.initColumnIndex; } /** * ???????? * * @param styleKey ? */ public static void insertSerial(SheetTemplate template, String styleKey) { if (template.serialNumberColumnIndex < 0) return; template.serialNumber++; Cell c = template.currentRow.createCell(template.serialNumberColumnIndex); setCellStyle(template, c, styleKey); c.setCellValue(template.serialNumber); } /** * <p>Excel??</p> * * @param value * @param styleKey ? */ public static void createCell(SheetTemplate template, Object value, String styleKey) { Cell cell = template.currentRow.createCell(template.currentColumnIndex); setCellStyle(template, cell, styleKey); if (null == value || "".equals(value)) { template.currentColumnIndex++; return; } if (String.class == value.getClass()) { cell.setCellValue((String) value); template.currentColumnIndex++; return; } if (int.class == value.getClass()) { cell.setCellValue((int) value); template.currentColumnIndex++; return; } if (Integer.class == value.getClass()) { cell.setCellValue((Integer) value); template.currentColumnIndex++; return; } if (double.class == value.getClass()) { cell.setCellValue((double) value); template.currentColumnIndex++; return; } if (Double.class == value.getClass()) { cell.setCellValue((Double) value); template.currentColumnIndex++; return; } if (Date.class == value.getClass()) { cell.setCellValue((Date) value); template.currentColumnIndex++; return; } if (boolean.class == value.getClass()) { cell.setCellValue((boolean) value); template.currentColumnIndex++; return; } if (Boolean.class == value.getClass()) { cell.setCellValue((Boolean) value); template.currentColumnIndex++; return; } if (Calendar.class == value.getClass()) { cell.setCellValue((Calendar) value); template.currentColumnIndex++; return; } if (RichTextString.class == value.getClass()) { cell.setCellValue((RichTextString) value); template.currentColumnIndex++; return; } // default value#toString cell.setCellValue(value.toString()); template.currentColumnIndex++; } /** * ?? * * @param cell cell * @param styleKey ? */ private static void setCellStyle(SheetTemplate template, Cell cell, String styleKey) { if (null != styleKey && null != template.classifyStyle.get(styleKey)) { cell.setCellStyle(template.classifyStyle.get(styleKey)); return; } if (null != template.appointLineStyle && template.appointLineStyle.containsKey(cell.getRowIndex())) { cell.setCellStyle(template.appointLineStyle.get(cell.getRowIndex())); return; } if (null != template.singleLineStyle && (cell.getRowIndex() % 2 != 0)) { cell.setCellStyle(template.singleLineStyle); return; } if (null != template.doubleLineStyle && (cell.getRowIndex() % 2 == 0)) { cell.setCellStyle(template.doubleLineStyle); return; } if (null != template.defaultStyle) cell.setCellStyle(template.defaultStyle); } /*-----------------------------------??-----------------------------------*/ }