Java tutorial
package com.test.demo.ccbpay; import au.com.bytecode.opencsv.CSVReader; import org.apache.commons.lang.ArrayUtils; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.util.SAXHelper; import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler; import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.XSSFComment; import org.xml.sax.ContentHandler; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import javax.xml.parsers.ParserConfigurationException; import java.io.*; import java.math.BigDecimal; import java.util.ArrayList; import java.util.List; /* ==================================================================== 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. ==================================================================== */ /** * A rudimentary XLSX -> CSV processor modeled on the * POI sample program XLS2CSVmra from the package * org.apache.poi.hssf.eventusermodel.examples. * As with the HSSF version, this tries to spot missing * rows and cells, and output empty entries for them. * <p/> * Data sheets are read using a SAX parser to keep the * memory footprint relatively small, so this should be * able to read enormous workbooks. The styles table and * the shared-string table must be kept in memory. The * standard POI styles table class is used, but a custom * (read-only) class is used for the shared string table * because the standard POI SharedStringsTable grows very * quickly with the number of unique strings. * <p/> * For a more advanced implementation of SAX event parsing * of XLSX files, see {@link XSSFEventBasedExcelExtractor} * and {@link XSSFSheetXMLHandler}. Note that for many cases, * it may be possible to simply use those with a custom * {@link SheetContentsHandler} and no SAX code needed of * your own! */ /** * XLSX? */ public class XLSX2CSV { /** * Uses the XSSF Event SAX helpers to do most of the work * of parsing the Sheet XML, and outputs the contents * as a (basic) CSV. */ private List<String[]> rows = new ArrayList<>(); private final OPCPackage xlsxPackage; /** * Number of columns to read starting with leftmost */ private int minColumns; /** * Destination for data */ private class SheetToCSV implements SheetContentsHandler { private String[] record; private int minColumns; private int thisColumn = 0; SheetToCSV(int minColumns) { super(); this.minColumns = minColumns; } @Override public void startRow(int rowNum) { record = new String[this.minColumns]; } @Override public void endRow(int rowNum) { thisColumn = 0; rows.add(this.record); } @Override public void cell(String cellReference, String formattedValue, XSSFComment comment) { if (thisColumn < this.minColumns) record[thisColumn] = formattedValue; thisColumn++; } @Override public void headerFooter(String text, boolean isHeader, String tagName) { // Skip, no headers or footers in CSV } } /** * Creates a new XLSX -> CSV converter * * @param pkg The XLSX package to process * @param minColumns The minimum number of columns to output, or -1 for no minimum */ public XLSX2CSV(OPCPackage pkg, int minColumns) { this.xlsxPackage = pkg; this.minColumns = minColumns; } /** * Parses and shows the content of one sheet * using the specified styles and shared-strings tables. * * @param styles * @param strings * @param sheetInputStream */ public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler, InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException { DataFormatter formatter = new DataFormatter(); InputSource sheetSource = new InputSource(sheetInputStream); try { XMLReader sheetParser = SAXHelper.newXMLReader(); ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false); sheetParser.setContentHandler(handler); sheetParser.parse(sheetSource); } catch (ParserConfigurationException e) { throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage()); } } /** * Initiates the processing of the XLS workbook file to CSV. * * @throws IOException * @throws OpenXML4JException * @throws ParserConfigurationException * @throws SAXException */ private List<String[]> process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException { ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage); XSSFReader xssfReader = new XSSFReader(this.xlsxPackage); StylesTable styles = xssfReader.getStylesTable(); XSSFReader.SheetIterator it = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); while (it.hasNext()) { InputStream stream = it.next(); processSheet(styles, strings, new SheetToCSV(this.minColumns), stream); stream.close(); } return this.rows; } /** * excel * @param in ? * @param minColumns * @return list * @throws Exception */ public static List<String[]> getRecords(InputStream in, int minColumns) throws Exception { if (in == null) { throw new Exception("?null"); } // The package open is instantaneous, as it should be. OPCPackage p = OPCPackage.open(in); XLSX2CSV xlsx2csv = new XLSX2CSV(p, minColumns); List<String[]> list = xlsx2csv.process(); p.close(); return list; } /** * excel * @param filePath xlsx? * @param minColumns * @return list * @throws Exception */ public static List<String[]> getRecords(String filePath, int minColumns) throws Exception { File f = new File(filePath); if (!f.exists()) { throw new Exception("?"); } // The package open is instantaneous, as it should be. OPCPackage p = OPCPackage.open(f); XLSX2CSV xlsx2csv = new XLSX2CSV(p, minColumns); List<String[]> list = xlsx2csv.process(); p.close(); return list; } public static void main(String[] args) throws Exception { File f = new File( "C:\\Users\\dmall\\Desktop\\???\\1493142812All2018-05-15_2018-05-20.csv"); InputStreamReader reader = new InputStreamReader(new FileInputStream(f), "GBK"); CSVReader csvReader = new CSVReader(reader); String[] csvRow = null; // row long csvDataSize = 0; long pay0515 = 0L; long re0515 = 0L; long cost0515 = 0L; while ((csvRow = csvReader.readNext()) != null) { if (csvRow.length != 24) { continue; } csvDataSize += 1; if (csvDataSize > 1) { if (csvRow[0].contains("2018-05-17")) { pay0515 += BigDecimal.valueOf(Double.valueOf(csvRow[12].substring(1, csvRow[12].length()))) .multiply(new BigDecimal(100)).longValue(); re0515 += BigDecimal.valueOf(Double.valueOf(csvRow[16].substring(1, csvRow[16].length()))) .multiply(new BigDecimal(100)).longValue(); cost0515 += BigDecimal.valueOf(Double.valueOf(csvRow[22].substring(1, csvRow[22].length()))) .multiply(new BigDecimal(100)).longValue(); } } } System.out.println(pay0515); System.out.println(re0515); System.out.println(cost0515); System.out.println(pay0515 - re0515 - cost0515); } }