poi、easyexcel分别完美复制excel的Sheet

/* * ==================================================================== 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. * * 2012 - Alfresco Software, Ltd. Alfresco Software has modified source of this file The details of changes as svn diff * can be found in svn at location root/projects/3rd-party/src * ==================================================================== */ package com.alibaba.easyexcel.test.temp.dataformat; import java.math.BigDecimal; import java.math.RoundingMode; import java.text.DateFormat; import java.text.DateFormatSymbols; import java.text.DecimalFormat; import java.text.DecimalFormatSymbols; import java.text.FieldPosition; import java.text.Format; import java.text.ParsePosition; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.Observable; import java.util.Observer; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.poi.ss.format.CellFormat; import org.apache.poi.ss.format.CellFormatResult; import org.apache.poi.ss.formula.ConditionalFormattingEvaluator; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.ExcelGeneralNumberFormat; import org.apache.poi.ss.usermodel.ExcelNumberFormat; import org.apache.poi.ss.usermodel.ExcelStyleDateFormatter; import org.apache.poi.ss.usermodel.FormulaError; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.FractionFormat; import org.apache.poi.ss.util.DateFormatConverter; import org.apache.poi.ss.util.NumberToTextConverter; import org.apache.poi.util.LocaleUtil; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; /** * DataFormatter contains methods for formatting the value stored in an Cell. This can be useful for reports and GUI * presentations when you need to display data exactly as it appears in Excel. Supported formats include currency, SSN, * percentages, decimals, dates, phone numbers, zip codes, etc. * <p> * Internally, formats will be implemented using subclasses of {@link Format} such as {@link DecimalFormat} and * {@link java.text.SimpleDateFormat}. Therefore the formats used by this class must obey the same pattern rules as * these Format subclasses. This means that only legal number pattern characters ("0", "#", ".", "," etc.) may appear in * number formats. Other characters can be inserted <em>before</em> or <em> after</em> the number pattern to form a * prefix or suffix. * </p> * <p> * For example the Excel pattern <code>"$#,##0.00 "USD"_);($#,##0.00 "USD")" * </code> will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)". However the pattern * <code>"00-00-00"</code> is incorrectly formatted by DecimalFormat as "000000--". For Excel formats that are not * compatible with DecimalFormat, you can provide your own custom {@link Format} implementation via * <code>DataFormatter.addFormat(String,Format)</code>. The following custom formats are already provided by this class: * </p> * * <pre> * <ul><li>SSN "000-00-0000"</li> * <li>Phone Number "(###) ###-####"</li> * <li>Zip plus 4 "00000-0000"</li> * </ul> * </pre> * <p> * If the Excel format pattern cannot be parsed successfully, then a default format will be used. The default number * format will mimic the Excel General format: "#" for whole numbers and "#.##########" for decimal numbers. You can * override the default format pattern with <code> * DataFormatter.setDefaultNumberFormat(Format)</code>. <b>Note:</b> the default format will only be used when a Format * cannot be created from the cell's data format string. * * <p> * Note that by default formatted numeric values are trimmed. Excel formats can contain spacers and padding and the * default behavior is to strip them off. * </p> * <p> * Example: * </p> * <p> * Consider a numeric cell with a value <code>12.343</code> and format <code>"##.##_ "</code>. The trailing underscore * and space ("_ ") in the format adds a space to the end and Excel formats this cell as <code>"12.34 "</code>, but * <code>DataFormatter</code> trims the formatted value and returns <code>"12.34"</code>. * </p> * You can enable spaces by passing the <code>emulateCSV=true</code> flag in the <code>DateFormatter</code> cosntructor. * If set to true, then the output tries to conform to what you get when you take an xls or xlsx in Excel and Save As * CSV file: * <ul> * <li>returned values are not trimmed</li> * <li>Invalid dates are formatted as 255 pound signs ("#")</li> * <li>simulate Excel's handling of a format string of all # when the value is 0. Excel will output "", * <code>DataFormatter</code> will output "0". * </ul> * <p> * Some formats are automatically "localized" by Excel, eg show as mm/dd/yyyy when loaded in Excel in some Locales but * as dd/mm/yyyy in others. These are always returned in the "default" (US) format, as stored in the file. Some format * strings request an alternate locale, eg <code>[$-809]d/m/yy h:mm AM/PM</code> which explicitly requests UK locale. * These locale directives are (currently) ignored. You can use {@link DateFormatConverter} to do some of this * localisation if you need it. */ public class DataFormatter1 implements Observer { private static final String defaultFractionWholePartFormat = "#"; private static final String defaultFractionFractionPartFormat = "#/##"; /** Pattern to find a number format: "0" or "#" */ private static final Pattern numPattern = Pattern.compile("[0#]+"); /** Pattern to find days of week as text "ddd...." */ private static final Pattern daysAsText = Pattern.compile("([d]{3,})", Pattern.CASE_INSENSITIVE); /** Pattern to find "AM/PM" marker */ private static final Pattern amPmPattern = Pattern.compile("((A|P)[M/P]*)", Pattern.CASE_INSENSITIVE); /** Pattern to find formats with condition ranges e.g. [>=100] */ private static final Pattern rangeConditionalPattern = Pattern.compile(".*\\[\\s*(>|>=|<|<=|=)\\s*[0-9]*\\.*[0-9].*"); /** * A regex to find locale patterns like [$$-1009] and [$?-452]. Note that we don't currently process these into * locales */ private static final Pattern localePatternGroup = Pattern.compile("(\\[\\$[^-\\]]*-[0-9A-Z]+\\])"); /** * A regex to match the colour formattings rules. Allowed colours are: Black, Blue, Cyan, Green, Magenta, Red, * White, Yellow, "Color n" (1<=n<=56) */ private static final Pattern colorPattern = Pattern.compile("(\\[BLACK\\])|(\\[BLUE\\])|(\\[CYAN\\])|(\\[GREEN\\])|" + "(\\[MAGENTA\\])|(\\[RED\\])|(\\[WHITE\\])|(\\[YELLOW\\])|" + "(\\[COLOR\\s*\\d\\])|(\\[COLOR\\s*[0-5]\\d\\])", Pattern.CASE_INSENSITIVE); /** * A regex to identify a fraction pattern. This requires that replaceAll("\\?", "#") has already been called */ private static final Pattern fractionPattern = Pattern.compile("(?:([#\\d]+)\\s+)?(#+)\\s*\\/\\s*([#\\d]+)"); /** * A regex to strip