package top.yangcourage.test.poi;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class PoiReadAndWrite {
final static String filePath = "X:\\xxxx\\测试文件内容.xlsx";
public static void main(String[] args) throws Exception {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
DecimalFormat df = new DecimalFormat("0.00");
FormulaEvaluator fe = null;
InputStream input = new FileInputStream(new File(filePath));
Workbook wb = null;
if(filePath.endsWith(".xlsx")){
wb = new XSSFWorkbook(input);
}
else{
wb = new HSSFWorkbook(input);
}
fe = wb.getCreationHelper().createFormulaEvaluator();
int sheetCount = wb.getNumberOfSheets();
Sheet sheet = wb.getSheetAt(0);
XSSFDrawing drawing = (XSSFDrawing)sheet.getDrawingPatriarch();
int rowCount = sheet.getLastRowNum();
for(int i=0;i<rowCount;i++){
Row row = sheet.getRow(i);
if(null == row){
continue;
}
int columnCount = row.getLastCellNum();
for(int j=0;j<columnCount;j++){
Cell cell = row.getCell(j);
XSSFCellStyle style = (XSSFCellStyle)cell.getCellStyle();
CellRangeAddress cra = getRangeAddress(sheet,i,j);
if(null != cra){
System.out.print("合并节点,数值在:"+cra.getFirstRow()+":"+cra.getFirstColumn());
continue;
}
if(null != cell){
CellType type = cell.getCellTypeEnum();
if(type == CellType.STRING){
System.out.print("String:"+cell.getStringCellValue());
}
else if(type == CellType.FORMULA){
System.out.print("公式型:"+cell.getCellFormula()+":"+df.format(fe.evaluate(cell).getNumberValue()));
}
else if(type == CellType.NUMERIC){
if(DateUtil.isCellDateFormatted(cell)){
System.out.print("日期型:"+format.format(cell.getDateCellValue()));
}
else if("General".equals(cell.getCellStyle().getDataFormatString())){
System.out.print("文本型:"+df.format(cell.getNumericCellValue()));
}
else{
System.out.print("未解析类型:"+cell.getRichStringCellValue().toString());
}
}
else if(type == CellType.BOOLEAN){
System.out.print(cell.getBooleanCellValue());
}
else if(type == CellType.ERROR){
System.out.print("错误值为:"+cell.getErrorCellValue());
}
else if(type == CellType.BLANK){
System.out.print("空白内容");
}
else if(type == CellType._NONE){
System.out.print("null");
}
else{
System.out.print("Read Error");
}
}
}
System.out.println();
}
}
public static CellRangeAddress getRangeAddress(Sheet sheet,int row,int column){
int len = sheet.getNumMergedRegions();
for(int i=0;i<len;i++){
CellRangeAddress cra = sheet.getMergedRegion(i);
boolean rowInBool = row >= cra.getFirstRow() && row <= cra.getLastRow();
boolean colInBool = column >= cra.getFirstColumn() && column <= cra.getLastColumn();
if(rowInBool && colInBool){
return cra;
}
}
return null;
}
}
package top.yangcourage.test.poi;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import org.xml.sax.SAXException;
public class PoiReadByStream {
public void processOneSheet(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
InputStream sheet2 = r.getSheet("rId1");
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
public void processFirstSheet(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
InputStream sheet2 = null;
Iterator< InputStream > sheets = r.getSheetsData();
if(sheets.hasNext()) sheet2 = sheets.next();
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
XMLReader parser =
XMLReaderFactory.createXMLReader(
"org.apache.xerces.parsers.SAXParser"
);
ContentHandler handler = new SheetHandler(sst);
parser.setContentHandler(handler);
return parser;
}
private static class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if ( "inlineStr".equals( name ) || "v".equals( name ) )
{
String cellType = attributes.getValue("t");
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
else
if(name.equals("c")) {
System.out.print(attributes.getValue("r") + " - ");
String cellType = attributes.getValue("t");
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
if(nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
if(name.equals("v")) {
System.out.println(lastContents);
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
}
public static void main(String[] args) {
PoiReadByStream example = new PoiReadByStream();
try {
example.processFirstSheet("X:\\xxx\xxx.xlsx");
} catch (Exception e) {
e.printStackTrace();
}
}
}