Java解析本地文件Excel文件

来源：互联网发布：印度现状知乎编辑：程序博客网时间：2024/05/21 21:47

1.解析本地Excel文件，必须添加的jar包依赖

<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->        <dependency>            <groupId>org.apache.xmlbeans</groupId>            <artifactId>xmlbeans</artifactId>            <version>2.6.0</version>        </dependency>        <!-- poi 2003 -->        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.15</version>        </dependency>        <!-- poi 2007 2010 -->        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.15</version>        </dependency>        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml-schemas</artifactId>            <version>3.15</version>        </dependency>

2.测试代码如下：

package cn.sh.util;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.text.DateFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ReadExcelTools {//XSSF -- 提供读写Microsoft Excel OOXML格式档案的功能//XSSFWorkbook:是操作Excel2007（以上）的版本，扩展名是.xlsxpublic static List<Model> read2007Xlsx(InputStream in) throws IOException, InvalidFormatException, ParseException {List<Model> list = new ArrayList<>();//XSSFWorkbook xWorkbook = new XSSFWorkbook(is);XSSFWorkbook xWorkbook = new XSSFWorkbook(in);// Read the SheetXSSFSheet xssfSheet = xWorkbook.getSheetAt(0);// Read the Rowfor (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {XSSFRow xssfRow = xssfSheet.getRow(rowNum);if (xssfRow != null) {Model voucher =new Model();xssfRow.getCell(1).setCellType(Cell.CELL_TYPE_STRING);xssfRow.getCell(2).setCellType(Cell.CELL_TYPE_STRING);xssfRow.getCell(4).setCellType(Cell.CELL_TYPE_STRING);xssfRow.getCell(6).setCellType(Cell.CELL_TYPE_STRING);xssfRow.getCell(8).setCellType(Cell.CELL_TYPE_STRING);xssfRow.getCell(10).setCellType(Cell.CELL_TYPE_STRING);xssfRow.getCell(6).setCellType(Cell.CELL_TYPE_STRING); String time = xssfRow.getCell(1).getStringCellValue();System.out.println(time);DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");  Date date = format1.parse(time);SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");System.out.println(format2.format(date));voucher.setTime(format2.format(date));//时间voucher.setName(xssfRow.getCell(2).getStringCellValue());voucher.setZjlx(xssfRow.getCell(4).getStringCellValue());voucher.setZjhm(xssfRow.getCell(6).getStringCellValue());//券密码if(!"".equals(xssfRow.getCell(8).getStringCellValue())){voucher.setPhone(xssfRow.getCell(8).getStringCellValue());//生成时间}else{voucher.setPhone("");}if(!"".equals(xssfRow.getCell(10).getStringCellValue())){voucher.setAddress(xssfRow.getCell(10).getStringCellValue());//截止有效时间}else{voucher.setAddress("");}voucher.setStatus("办结");//截止有效时间list.add(voucher);}}xWorkbook.close();return list;}public static void main(String[] args) throws InvalidFormatException, IOException, ParseException {String fullFileName = "C:/doc/收养数据导出市婚登.xlsx";InputStream is = new FileInputStream(fullFileName);List<Model> list = read2007Xlsx(is);System.out.println(list.size());}}

阅读全文

0 0