解析xlsx檔案---Java讀取Excel2007
關於Java讀取Excel2007的文章在Google、百度上搜尋一下，沒有太好的例子，實現的也不算太好。檢視了一下Poi，最新的 POI 3.5 beta 4 支援讀寫 Excel2007和PPT2007(XLSX and PPTX)，自己來實現Java讀取Excel2007了。
一、老版本案例，08年的poi.jar；下面案例只適用於與解析格式較小的檔案，對於較大的檔案會出現記憶體溢位情況。
1，下載 POI 3.5 beta 4 解壓，把其中的jar包匯入專案檔案。以我的讀取為例，匯入了以下jar包。
沒有配置 log4j，測試時報告警報資訊，應該為載入順序導致的初始化問題造成(暫時沒有找原因)。
2，建立讀取 Excel2007 方法
// 構造 XSSFWorkbook 物件，strPath 傳入檔案路徑
XSSFWorkbook xwb = new XSSFWorkbook(path); //已過時，被廢棄，使用會出錯
XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
// 讀取第一章表格內容
XSSFSheet sheet = xwb.getSheetAt(0);
// 定義 row、cell
XSSFRow row;
String cell;
// 迴圈輸出表格中的內容
for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
// 通過 row.getCell(j).toString() 獲取單元格內容，
cell = row.getCell(j).toString();
System.out.print(cell + "\t");
}
System.out.println("");
}
// 構造 XSSFWorkbook 物件，strPath 傳入檔案路徑
XSSFWorkbook xwb = new XSSFWorkbook(strPath);
// 讀取第一章表格內容
XSSFSheet sheet = xwb.getSheetAt(0);
// 定義 row、cell
XSSFRow row;
String cell;
// 迴圈輸出表格中的內容
for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
// 通過 row.getCell(j).toString() 獲取單元格內容，
cell = row.getCell(j).toString();
System.out.print(cell + "\t");
}
System.out.println("");
}
此過程直接傳入檔案所在路徑，其他一些過程已經被自動實現。
3，測試，檔案為8列3563行資料，以文字方式讀取內容，
public static void main(String[] args) {
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SS");
TimeZone t = sdf.getTimeZone();
t.setRawOffset(0);
sdf.setTimeZone(t);
Long startTime = System.currentTimeMillis();
String fileName = "F:\\我的文件\\學生繳費程式碼.xlsx";
// 檢測程式碼
try {
PoiReadExcel er = new PoiReadExcel();
// 讀取excel2007
er.testPoiExcel2007(fileName);
} catch (Exception ex) {
Logger.getLogger(FastexcelReadExcel.class.getName()).log(Level.SEVERE, null, ex);
}
Long endTime = System.currentTimeMillis();
System.out.println("用時：" + sdf.format(new Date(endTime - startTime)));
}
public static void main(String[] args) {
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SS");
TimeZone t = sdf.getTimeZone();
t.setRawOffset(0);
sdf.setTimeZone(t);
Long startTime = System.currentTimeMillis();
String fileName = "F:\\我的文件\\學生繳費程式碼.xlsx";
// 檢測程式碼
try {
PoiReadExcel er = new PoiReadExcel();
// 讀取excel2007
er.testPoiExcel2007(fileName);
} catch (Exception ex) {
Logger.getLogger(FastexcelReadExcel.class.getName()).log(Level.SEVERE, null, ex);
}
Long endTime = System.currentTimeMillis();
System.out.println("用時：" + sdf.format(new Date(endTime - startTime)));
}
讀取所用時間為：4.172秒。
二、java解析大資料分析
一. Apache POI 簡介（ http://poi.apache.org/）
使用Java程式讀寫Microsoft Office，提供了下面這幾種型別：
HSSF－提供讀寫Microsoft Excel XLS格式檔案的功能。
XSSF－提供讀寫Microsoft Excel OOXML XLSX格式檔案的功能。
HWPF－提供讀寫Microsoft Word DOC格式檔案的功能。
HSLF－供讀寫Microsoft PowerPoint格式檔案的功能。
HDGF－提供讀Microsoft Visio格式檔案的功能。
HPBF－提供讀Microsoft Publisher格式檔案的功能。
二、POI操作Excel
1. 官方快速幫助：http://poi.apache.org/spreadsheet/quick-guide.html
2. 匯入包：poi-3.6.jar
參考：
1. http://www.blogjava.net/vwpolo/archive/2009/09/16/295243.html
2. http://hacker-zxf.javaeye.com/blog/746546
3. http://zmx.javaeye.com/blog/622536
4. http://canfly2010.javaeye.com/blog/701726
[java] view plaincopyprint?
package excel.poi.input;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.POITextExtractor;
import org.apache.poi.extractor.ExtractorFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.XmlException;
public class ReadExcel {
/**
* 讀取office 2003 xls
* @param filePath
*/
@SuppressWarnings({ "unchecked", "deprecation" })
public void loadXls(String filePath){
try {
InputStream input = new FileInputStream("D://test.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
// Iterate over each row in the sheet
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
System.out.println("Row #" + row.getRowNum());
// Iterate over each cell in the row and print out the cell"s
// content
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
System.out.println("Cell #" + cell.getCellNum());
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println("unsuported sell type");
break;
}
}
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
/**
* 讀取xlsx文字
* @param filePath
*/
public void loadXlsxText(String filePath){
File inputFile = new File("D://test.xlsx");
try {
POITextExtractor extractor = ExtractorFactory.createExtractor(inputFile);
System.out.println(extractor.getText());
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (OpenXML4JException e) {
e.printStackTrace();
} catch (XmlException e) {
e.printStackTrace();
}
}
/**
* 讀取office 2007 xlsx
* @param filePath
*/
public void loadXlsx(String filePath){
// 構造 XSSFWorkbook 物件，strPath 傳入檔案路徑
XSSFWorkbook xwb = null;
try {
xwb = new XSSFWorkbook("D://test.xlsx");
} catch (IOException e) {
System.out.println("讀取檔案出錯");
e.printStackTrace();
}
// 讀取第一章表格內容
XSSFSheet sheet = xwb.getSheetAt(0);
xwb.getSheetAt(1);
// 定義 row、cell
XSSFRow row;
String cell;
// 迴圈輸出表格中的內容
for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
// 通過 row.getCell(j).toString() 獲取單元格內容，
cell = row.getCell(j).toString();
System.out.print(cell + "/t");
}
System.out.println("");
}
}
public static void main(String[] args) {
ReadExcel readExcel =new ReadExcel();
readExcel.loadXlsx("");
}
}
package excel.poi.input;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.POITextExtractor;
import org.apache.poi.extractor.ExtractorFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.XmlException;
public class ReadExcel {
/**
* 讀取office 2003 xls
* @param filePath
*/
@SuppressWarnings({ "unchecked", "deprecation" })
public void loadXls(String filePath){
try {
InputStream input = new FileInputStream("D://test.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
// Iterate over each row in the sheet
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
System.out.println("Row #" + row.getRowNum());
// Iterate over each cell in the row and print out the cell"s
// content
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
System.out.println("Cell #" + cell.getCellNum());
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println("unsuported sell type");
break;
}
}
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
/**
* 讀取xlsx文字
* @param filePath
*/
public void loadXlsxText(String filePath){
File inputFile = new File("D://test.xlsx");
try {
POITextExtractor extractor = ExtractorFactory.createExtractor(inputFile);
System.out.println(extractor.getText());
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (OpenXML4JException e) {
e.printStackTrace();
} catch (XmlException e) {
e.printStackTrace();
}
}
/**
* 讀取office 2007 xlsx
* @param filePath
*/
public void loadXlsx(String filePath){
// 構造 XSSFWorkbook 物件，strPath 傳入檔案路徑
XSSFWorkbook xwb = null;
try {
xwb = new XSSFWorkbook("D://test.xlsx");
} catch (IOException e) {
System.out.println("讀取檔案出錯");
e.printStackTrace();
}
// 讀取第一章表格內容
XSSFSheet sheet = xwb.getSheetAt(0);
xwb.getSheetAt(1);
// 定義 row、cell
XSSFRow row;
String cell;
// 迴圈輸出表格中的內容
for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
// 通過 row.getCell(j).toString() 獲取單元格內容，
cell = row.getCell(j).toString();
System.out.print(cell + "/t");
}
System.out.println("");
}
}
public static void main(String[] args) {
ReadExcel readExcel =new ReadExcel();
readExcel.loadXlsx("");
}
}
[java] view plaincopyprint?
<SPAN style="COLOR: #ff0000">但是：Workbook wb = WorkbookFactory.create(new FileInputStream(FILE_URL));</SPAN>
但是：Workbook wb = WorkbookFactory.create(new FileInputStream(FILE_URL));[java] view plaincopyprint?
<SPAN style="COLOR: #ff0000">檢視Create函式的原始碼：</SPAN>
三、java解析excel大資料；臨時表的形式
此篇是緊接上篇《大資料量的excel檔案讀取——2003及之前版本》。
excel2007檔案格式與之前版本不同，之前版本採用的是微軟自己的儲存格式。07版內容的儲存採用XML格式，所以，理所當然的，對大資料量的xlsx檔案的讀取採用的也是XML的處理方式SAX。
同之前的版本一樣，大資料量檔案的讀取採用的是事件模型eventusermodel。usermodel模式需要將檔案一次性全部讀到記憶體中，07版的既然採用的儲存模式是xml，解析用的DOM方式也是如此，這種模式操作簡單，容易上手，但是對於大量資料佔用的記憶體也是相當可觀，在Eclipse中經常出現記憶體溢位。
下面就是採用eventusermodel對07excel檔案讀取。
同上篇，我將當前行的單元格資料儲存到List中，抽象出 optRows 方法，該方法會在每行末尾時呼叫，方法引數為當前行索引curRow(int型)及存有行內單元格資料的List。繼承類只需實現該行級方法即可。
補充：今天發現讀取2007的指令碼存在存在一處問題，在遇到空單元格時會跳過該單元格，由於工作緊張沒有時間去解決該問題，這裡給出一個暫時的處理辦法。開啟檔案，在開始選單中選擇"查詢和選擇"，"定位條件"，選擇"空值"，確定，這時會找出所有的空單元格，直接按空格，然後Ctrl+enter，就會將所有空單元格填入一個空格，儲存即可。
2010.6.10補充：空單元格的問題已經解決，在2007的文件中空單元格是不儲存的，單元格的r屬性是單元格位置資訊，格式為[A-Z]+[0-9]+。字母部分是列索引，數字部分是行索引。
抽象類：XxlsAbstract ，作用：遍歷excel檔案，提供行級操作方法 optRows
package com.gaosheng.util.xls;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
/**
* XSSF and SAX (Event API)
*/
public abstract class XxlsAbstract extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private int sheetIndex = -1;
private List<String> rowlist = new ArrayList<String>();
private int curRow = 0; //當前行
private int curCol = 0; //當前列索引
private int preCol = 0; //上一列列索引
private int titleRow = 0; //標題行，一般情況下為0
private int rowsize = 0; //列數
//excel記錄行操作方法，以行索引和行元素列表為引數，對一行元素進行操作，元素為String型別
// public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;
//excel記錄行操作方法，以sheet索引，行索引和行元素列表為引數，對sheet的一行元素進行操作，元素為String型別
public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;
//只遍歷一個sheet，其中sheetId為要遍歷的sheet索引，從1開始，1-3
public void processOneSheet(String filename,int sheetId) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// rId2 found by processing the Workbook
// 根據 rId# 或 rSheet# 查詢sheet
InputStream sheet2 = r.getSheet("rId"+sheetId);
sheetIndex++;
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
/**
* 遍歷 excel 檔案
*/
public void process(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
while (sheets.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst)
throws SAXException {
XMLReader parser = XMLReaderFactory
.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => 單元格
if (name.equals("c")) {
// 如果下一個元素是 SST 的索引，則將nextIsString標記為true
String cellType = attributes.getValue("t");
String rowStr = attributes.getValue("r");
curCol = this.getRowIndex(rowStr);
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// 置空
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
// 根據SST的索引值的到單元格的真正要儲存的字串
// 這時characters()方法可能會被呼叫多次
if (nextIsString) {
try {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
.toString();
} catch (Exception e) {
}
}
// v => 單元格的值，如果單元格是字串則v標籤的值為該字串在SST中的索引
// 將單元格內容加入rowlist中，在這之前先去掉字串前後的空白符
if (name.equals("v")) {
String value = lastContents.trim();
value = value.equals("")?" ":value;
int cols = curCol-preCol;
if (cols>1){
for (int i = 0;i < cols-1;i++){
rowlist.add(preCol,"");
}
}
preCol = curCol;
rowlist.add(curCol-1, value);
}else {
//如果標籤名稱為 row ，這說明已到行尾，呼叫 optRows() 方法
if (name.equals("row")) {
int tmpCols = rowlist.size();
if(curRow>this.titleRow && tmpCols<this.rowsize){
for (int i = 0;i < this.rowsize-tmpCols;i++){
rowlist.add(rowlist.size(), "");
}
}
try {
optRows(sheetIndex,curRow,rowlist);
} catch (SQLException e) {
e.printStackTrace();
}
if(curRow==this.titleRow){
this.rowsize = rowlist.size();
}
rowlist.clear();
curRow++;
curCol = 0;
preCol = 0;
}
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
//得到單元格內容的值
lastContents += new String(ch, start, length);
}
//得到列索引，每一列c元素的r屬性構成為字母加數字的形式，字母組合為列索引，數字組合為行索引，
//如AB45,表示為第（A-A+1）*26+（B-A+1）*26列，45行
public int getRowIndex(String rowStr){
rowStr = rowStr.replaceAll("[^A-Z]", "");
byte[] rowAbc = rowStr.getBytes();
int len = rowAbc.length;
float num = 0;
for (int i=0;i<len;i++){
num += (rowAbc[i]-'A'+1)*Math.pow(26,len-i-1 );
}
return (int) num;
}
public int getTitleRow() {
return titleRow;
}
public void setTitleRow(int titleRow) {
this.titleRow = titleRow;
}
}
package com.gaosheng.util.xls;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
/**
* XSSF and SAX (Event API)
*/
public abstract class XxlsAbstract extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private int sheetIndex = -1;
private List<String> rowlist = new ArrayList<String>();
private int curRow = 0; //當前行
private int curCol = 0; //當前列索引
private int preCol = 0; //上一列列索引
private int titleRow = 0; //標題行，一般情況下為0
private int rowsize = 0; //列數
//excel記錄行操作方法，以行索引和行元素列表為引數，對一行元素進行操作，元素為String型別
// public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;
//excel記錄行操作方法，以sheet索引，行索引和行元素列表為引數，對sheet的一行元素進行操作，元素為String型別
public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;
//只遍歷一個sheet，其中sheetId為要遍歷的sheet索引，從1開始，1-3
public void processOneSheet(String filename,int sheetId) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// rId2 found by processing the Workbook
// 根據 rId# 或 rSheet# 查詢sheet
InputStream sheet2 = r.getSheet("rId"+sheetId);
sheetIndex++;
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
/**
* 遍歷 excel 檔案
*/
public void process(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
while (sheets.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst)
throws SAXException {
XMLReader parser = XMLReaderFactory
.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => 單元格
if (name.equals("c")) {
// 如果下一個元素是 SST 的索引，則將nextIsString標記為true
String cellType = attributes.getValue("t");
String rowStr = attributes.getValue("r");
curCol = this.getRowIndex(rowStr);
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// 置空
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
// 根據SST的索引值的到單元格的真正要儲存的字串
// 這時characters()方法可能會被呼叫多次
if (nextIsString) {
try {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
.toString();
} catch (Exception e) {
}
}
// v => 單元格的值，如果單元格是字串則v標籤的值為該字串在SST中的索引
// 將單元格內容加入rowlist中，在這之前先去掉字串前後的空白符
if (name.equals("v")) {
String value = lastContents.trim();
value = value.equals("")?" ":value;
int cols = curCol-preCol;
if (cols>1){
for (int i = 0;i < cols-1;i++){
rowlist.add(preCol,"");
}
}
preCol = curCol;
rowlist.add(curCol-1, value);
}else {
//如果標籤名稱為 row ，這說明已到行尾，呼叫 optRows() 方法
if (name.equals("row")) {
int tmpCols = rowlist.size();
if(curRow>this.titleRow && tmpCols<this.rowsize){
for (int i = 0;i < this.rowsize-tmpCols;i++){
rowlist.add(rowlist.size(), "");
}
}
try {
optRows(sheetIndex,curRow,rowlist);
} catch (SQLException e) {
e.printStackTrace();
}
if(curRow==this.titleRow){
this.rowsize = rowlist.size();
}
rowlist.clear();
curRow++;
curCol = 0;
preCol = 0;
}
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
//得到單元格內容的值
lastContents += new String(ch, start, length);
}
//得到列索引，每一列c元素的r屬性構成為字母加數字的形式，字母組合為列索引，數字組合為行索引，
//如AB45,表示為第（A-A+1）*26+（B-A+1）*26列，45行
public int getRowIndex(String rowStr){
rowStr = rowStr.replaceAll("[^A-Z]", "");
byte[] rowAbc = rowStr.getBytes();
int len = rowAbc.length;
float num = 0;
for (int i=0;i<len;i++){
num += (rowAbc[i]-'A'+1)*Math.pow(26,len-i-1 );
}
return (int) num;
}
public int getTitleRow() {
return titleRow;
}
public void setTitleRow(int titleRow) {
this.titleRow = titleRow;
}
}
繼承類：XxlsBig，作用：將資料轉出到資料庫臨時表
package com.gaosheng.util.examples.xls;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;
import com.gaosheng.util.xls.XxlsAbstract;
public class XxlsBig extends XxlsAbstract {
public static void main(String[] args) throws Exception {
XxlsBig howto = new XxlsBig("temp_table");
howto.processOneSheet("F:/new.xlsx",1);
howto.process("F:/new.xlsx");
howto.close();
}
public XxlsBig(String tableName) throws SQLException{
this.conn = getNew_Conn();
this.statement = conn.createStatement();
this.tableName = tableName;
}
private Connection conn = null;
private Statement statement = null;
private PreparedStatement newStatement = null;
private String tableName = "temp_table";
private boolean create = true;
public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
if (sheetIndex == 0 && curRow == 0) {
StringBuffer preSql = new StringBuffer("insert into " + tableName
+ " values(");
StringBuffer table = new StringBuffer("create table " + tableName
+ "(");
int c = rowlist.size();
for (int i = 0; i < c; i++) {
preSql.append("?,");
table.append(rowlist.get(i));
table.append(" varchar2(100) ,");
}
table.deleteCharAt(table.length() - 1);
preSql.deleteCharAt(preSql.length() - 1);
table.append(")");
preSql.append(")");
if (create) {
statement = conn.createStatement();
try{
statement.execute("drop table "+tableName);
}catch(Exception e){
}finally{
System.out.println("表 "+tableName+" 刪除成功");
}
if (!statement.execute(table.toString())) {
System.out.println("建立表 "+tableName+" 成功");
// return;
} else {
System.out.println("建立表 "+tableName+" 失敗");
return;
}
}
conn.setAutoCommit(false);
newStatement = conn.prepareStatement(preSql.toString());
} else if(curRow>0) {
// 一般行
int col = rowlist.size();
for (int i = 0; i < col; i++) {
newStatement.setString(i + 1, rowlist.get(i).toString());
}
newStatement.addBatch();
if (curRow % 1000 == 0) {
newStatement.executeBatch();
conn.commit();
}
}
}
private static Connection getNew_Conn() {
Connection conn = null;
Properties props = new Properties();
FileInputStream fis = null;
try {
fis = new FileInputStream("D:/database.properties");
props.load(fis);
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// String jdbcURLString =
// "jdbc:oracle:thin:@192.168.0.28:1521:orcl";
StringBuffer jdbcURLString = new StringBuffer();
jdbcURLString.append("jdbc:oracle:thin:@");
jdbcURLString.append(props.getProperty("host"));
jdbcURLString.append(":");
jdbcURLString.append(props.getProperty("port"));
jdbcURLString.append(":");
jdbcURLString.append(props.getProperty("database"));
conn = DriverManager.getConnection(jdbcURLString.toString(), props
.getProperty("user"), props.getProperty("password"));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return conn;
}
public int close() {
try {
newStatement.executeBatch();
conn.commit();
System.out.println("資料寫入完畢");
this.newStatement.close();
this.statement.close();
this.conn.close();
return 1;
} catch (SQLException e) {
return 0;
}
}
}
package com.gaosheng.util.examples.xls;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;
import com.gaosheng.util.xls.XxlsAbstract;
public class XxlsBig extends XxlsAbstract {
public static void main(String[] args) throws Exception {
XxlsBig howto = new XxlsBig("temp_table");
howto.processOneSheet("F:/new.xlsx",1);
howto.process("F:/new.xlsx");
howto.close();
}
public XxlsBig(String tableName) throws SQLException{
this.conn = getNew_Conn();
this.statement = conn.createStatement();
this.tableName = tableName;
}
private Connection conn = null;
private Statement statement = null;
private PreparedStatement newStatement = null;
private String tableName = "temp_table";
private boolean create = true;
public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
if (sheetIndex == 0 && curRow == 0) {
StringBuffer preSql = new StringBuffer("insert into " + tableName
+ " values(");
StringBuffer table = new StringBuffer("create table " + tableName
+ "(");
int c = rowlist.size();
for (int i = 0; i < c; i++) {
preSql.append("?,");
table.append(rowlist.get(i));
table.append(" varchar2(100) ,");
}
table.deleteCharAt(table.length() - 1);
preSql.deleteCharAt(preSql.length() - 1);
table.append(")");
preSql.append(")");
if (create) {
statement = conn.createStatement();
try{
statement.execute("drop table "+tableName);
}catch(Exception e){
}finally{
System.out.println("表 "+tableName+" 刪除成功");
}
if (!statement.execute(table.toString())) {
System.out.println("建立表 "+tableName+" 成功");
// return;
} else {
System.out.println("建立表 "+tableName+" 失敗");
return;
}
}
conn.setAutoCommit(false);
newStatement = conn.prepareStatement(preSql.toString());
} else if(curRow>0) {
// 一般行
int col = rowlist.size();
for (int i = 0; i < col; i++) {
newStatement.setString(i + 1, rowlist.get(i).toString());
}
newStatement.addBatch();
if (curRow % 1000 == 0) {
newStatement.executeBatch();
conn.commit();
}
}
}
private static Connection getNew_Conn() {
Connection conn = null;
Properties props = new Properties();
FileInputStream fis = null;
try {
fis = new FileInputStream("D:/database.properties");
props.load(fis);
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// String jdbcURLString =
// "jdbc:oracle:thin:@192.168.0.28:1521:orcl";
StringBuffer jdbcURLString = new StringBuffer();
jdbcURLString.append("jdbc:oracle:thin:@");
jdbcURLString.append(props.getProperty("host"));
jdbcURLString.append(":");
jdbcURLString.append(props.getProperty("port"));
jdbcURLString.append(":");
jdbcURLString.append(props.getProperty("database"));
conn = DriverManager.getConnection(jdbcURLString.toString(), props
.getProperty("user"), props.getProperty("password"));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return conn;
}
public int close() {
try {
newStatement.executeBatch();
conn.commit();
System.out.println("資料寫入完畢");
this.newStatement.close();
this.statement.close();
this.conn.close();
return 1;
} catch (SQLException e) {
return 0;
}
}
}
繼承類：XxlsPrint，作用：將資料輸出到控制檯
package com.gaosheng.util.examples.xls;
import java.sql.SQLException;
import java.util.List;
import com.gaosheng.util.xls.XxlsAbstract;
public class XxlsPrint extends XxlsAbstract {
@Override
public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
for (int i = 0; i < rowlist.size(); i++) {
System.out.print("'" + rowlist.get(i) + "',");
}
System.out.println();
}
public static void main(String[] args) throws Exception {
XxlsPrint howto = new XxlsPrint();
howto.processOneSheet("F:/new.xlsx",1);
// howto.processAllSheets("F:/new.xlsx");
}
}
package com.gaosheng.util.examples.xls;
import java.sql.SQLException;
import java.util.List;
import com.gaosheng.util.xls.XxlsAbstract;
public class XxlsPrint extends XxlsAbstract {
@Override
public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
for (int i = 0; i < rowlist.size(); i++) {
System.out.print("'" + rowlist.get(i) + "',");
}
System.out.println();
}
public static void main(String[] args) throws Exception {
XxlsPrint howto = new XxlsPrint();
howto.processOneSheet("F:/new.xlsx",1);
// howto.processAllSheets("F:/new.xlsx");
}
}
原始碼在附件中，還包含了說明檔案、資料庫配置檔案、以及整合xls檔案和xlsx檔案讀取的類：Xls2Do。
excelutil.zip (87.6 KB)
下載次數: 65
相關文章
- Java 讀取檔案Java
- Qxlsx庫解析xlsx檔案問題
- Java使用xlsx-streamer和EasyExcel解決讀取超大excel檔案資料處理方法JavaExcel
- java中讀取配置檔案Java
- Java系列：讀取XML檔案JavaXML
- Java讀取Json檔案工具類JavaJSON
- Java 檔案處理完全指南：建立、讀取、寫入和刪除檔案詳細解析Java
- Java實時讀取日誌檔案Java
- java讀取大檔案並處理Java
- Java 讀取txt檔案生成Word文件Java
- java Spring讀取properties檔案的注意點JavaSpring
- Java使用commons-configuration讀取配置檔案Java
- Java讀取properties配置檔案工具包Java
- Java中的獲取檔案的物理絕對路徑，和讀取檔案Java
- Python3 進行讀取、修改和寫Excel表格(.xlsx檔案)的常用功能示例PythonExcel
- 任意檔案讀取
- java檔案相關（檔案追加內容、檔案內容清空、檔案內容讀取）Java
- Java讀取properties檔案連線資料庫Java資料庫
- Java讀取本地檔案內容並輸出Java
- Java動態指令碼Groovy讀取配置檔案Java指令碼
- Python 高階程式設計：深入解析 CSV 檔案讀取Python程式設計
- java class檔案解析Java
- go配置檔案讀取Go
- python讀取大檔案Python
- springboot讀取配置檔案Spring Boot
- 用友任意檔案讀取
- viper 讀取配置檔案
- matlab讀取npy檔案Matlab
- python小白檔案讀取Python
- cocos讀取plist檔案
- python 讀取文字檔案Python
- IOC - 讀取配置檔案
- 前端讀取excel檔案前端Excel
- pandas 無法開啟 .xlsx 檔案
- Mendmix程式碼解析:百搭的配置檔案讀取工具ResourceUtils
- java解析yaml配置檔案JavaYAML
- 使用 Java 解析XML檔案JavaXML
- java class 檔案格式解析Java
- 讀取檔案流並寫入檔案流