转csv 并读取

public class Excel_reader {

// *************xlsx文件读取函数************************

// excel_name为文件名，arg为需要查询的列号(输入数字则返回对应列 , 输入字符串则固定返回这个字符串)

// 返回

@SuppressWarnings({ "resource", "unused" })

public static List<PageData> xlsx_reader(String filepath,int args)

throws IOException {

File xlsxFile = new File(filepath+"\\userexcel.xlsx");

if (!xlsxFile.exists()) {

System.err.println("Not found or not a file: " + xlsxFile.getPath());

return null;

}

ArrayList<ArrayList<String>> excel_output = new ArrayList<ArrayList<String>>();

try {

OPCPackage p;

p = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ);

XLSX2CSVS xlsx2csv = new XLSX2CSVS(p, 20); // 20代表最大列数

xlsx2csv.process();

excel_output = xlsx2csv.get_output();

p.close(); //释放

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

System.out.println(filepath + " 读取完毕");

List<PageData> ans = new ArrayList<PageData>();

//ArrayList<ArrayList<String>> ans = new ArrayList<ArrayList<String>>();

// 遍历xlsx中的sheet

// 对于每个sheet，读取其中的每一行

for (int rowNum = 2; rowNum < excel_output.size(); rowNum++) {

ArrayList<String> cur_output = excel_output.get(rowNum);

PageData varpd =new PageData();

for (int columnNum = 0; columnNum < args; columnNum++) {

String cell = cur_output.get(columnNum);

varpd.put("var"+columnNum, cell);

}

ans.add(varpd);

}

return ans;

}

public static void main(String[] args) throws IOException

{

Excel_reader.xlsx_reader("D:\\已确认商品分类表114266.xlsx", 8);

}

}

 

 

public class XLSX2CSVS{

private class SheetToCSV implements SheetContentsHandler {

private boolean firstCellOfRow = false;

private int currentRow = -1;

private int currentCol = -1;

private void outputMissingRows(int number) {

for (int i = 0; i < number; i++) {

curstr = new ArrayList<String>();

for (int j = 0; j < minColumns; j++) {

curstr.add(null);

}

output.add(curstr);

}

}

@Override

public void startRow(int rowNum) {

curstr = new ArrayList<String>();

// If there were gaps, output the missing rows

outputMissingRows(rowNum - currentRow - 1);

// Prepare for this row

firstCellOfRow = true;

currentRow = rowNum;

currentCol = -1;

}

@Override

public void endRow(int rowNum) {

// Ensure the minimum number of columns

for (int i = currentCol; i < minColumns ; i++) {

curstr.add(null);

}

output.add(curstr);

}

@Override

public void cell(String cellReference, String formattedValue,

XSSFComment comment) {

// gracefully handle missing CellRef here in a similar way as XSSFCell does

if (cellReference == null) {

cellReference = new CellAddress(currentRow, currentCol).formatAsString();

}

// Did we miss any cells?

int thisCol = (new CellReference(cellReference)).getCol();

int missedCols = thisCol - currentCol - 1;

for (int i = 0; i < missedCols; i++) {

curstr.add(null);

}

currentCol = thisCol;

// Number or string?

try {

Double.parseDouble(formattedValue);

curstr.add(formattedValue);

} catch (NumberFormatException e) {

// output.append('"');

curstr.add(formattedValue);

// output.append('"');

}

}

@Override

public void headerFooter(String text, boolean isHeader, String tagName) {

// Skip, no headers or footers in CSV

}

}

private final OPCPackage xlsxPackage;

private final int minColumns;

private ArrayList<ArrayList<String>> output;

private ArrayList<String> curstr;

public ArrayList<ArrayList<String>> get_output(){

return output;

}

public XLSX2CSVS(OPCPackage pkg, int minColumns) {

this.xlsxPackage = pkg;

this.minColumns = minColumns;

}

 

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());

}

}

public void process()

throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {

ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);

XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);

StylesTable styles = xssfReader.getStylesTable();

XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();

int index = 0;

while (iter.hasNext()) {

output = new ArrayList<ArrayList<String>> ();

InputStream stream = iter.next();

String sheetName = iter.getSheetName();

System.out.println("正在读取sheet： "+sheetName + " [index=" + index + "]:");

processSheet(styles, strings, new SheetToCSV(), stream);

System.out.println("sheet 读取完成!");

stream.close();

++index;

}

}

}

 

/**

* ClassName:ObjectExcelRead

* @Title:

* @Description :(这里用一句话描述这个方法的作用).

* @author 黄木彬

* @version

* @since JDK 1.7

* Date: 2018-9-4 下午4:35:55

* Copyright (c) 2018, 上海凌天信息科技有限公司

* @see

*/

public class ObjectExcelRead {

/**

* @param filepath //文件路径

* @param filename //文件名

* @param startrow //开始行号

* @param startcol //开始列号

* @param sheetnum //sheet

* @return list

*/

public static List<PageData> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {

List<PageData> varList = new ArrayList<PageData>();

 

try {

boolean isExcel2003 = true;

//判断文件类型

if(CEVUtil.isExcel2007(filepath+"\\userexcel.xlsx")){

isExcel2003 = false;

}

File target = new File(filepath, filename);

FileInputStream fi = new FileInputStream(target);

Workbook wb =null;

if (isExcel2003){

wb = new HSSFWorkbook(fi);}

else{

return Excel_reader.xlsx_reader(filepath,8);

}

Sheet sheet = wb.getSheetAt(sheetnum);

//sheet 从0开始

int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号

 

for (int i = startrow; i < rowNum; i++) { //行循环开始

PageData varpd = new PageData();

Row row = sheet.getRow(i); //行

int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置

 

for (int j = startcol; j < cellNum; j++) { //列循环开始

Cell cell = row.getCell(Short.parseShort(j + ""));

String cellValue = null;

if (null != cell) {

switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库

case 0:

cellValue = String.valueOf((int) cell.getNumericCellValue());

break;

case 1:

cellValue = cell.getStringCellValue();

break;

case 2:

cellValue = cell.getNumericCellValue() + "";

// cellValue = String.valueOf(cell.getDateCellValue());

break;

case 3:

cellValue = "";

break;

case 4:

cellValue = String.valueOf(cell.getBooleanCellValue());

break;

case 5:

cellValue = String.valueOf(cell.getErrorCellValue());

break;

}

} else {

cellValue = "";

}

varpd.put("var"+j, cellValue);

}

varList.add(varpd);

}

 

} catch (Exception e) {

System.out.println(e);

}

return varList;

}

}