package frame.file.office;
|
|
import java.util.Map;
|
|
import javax.annotation.processing.Processor;
|
|
import org.apache.poi.ss.format.CellFormat;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.xssf.model.SharedStringsTable;
|
import org.dom4j.rule.Stylesheet;
|
|
import frame.data.EntitySet;
|
import frame.file.FileIOContext;
|
import frame.file.FileIOItem;
|
|
public class ExcelHandler {
|
private static char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray();
|
|
protected Processor progressor;
|
protected Map<String, String> parameterProvider;
|
protected OpenXmlWriter writer;
|
protected OpenXmlReader reader;
|
protected ColumnReader columnReader;
|
|
protected SpreadsheetDocument sheetDocument;
|
protected WorkbookPart workbookPart;
|
protected Stylesheet styleSheet;
|
protected SharedStringsTable sharedStringTable;
|
protected CellFormat cellFormat;
|
protected Sheets sheets;
|
|
protected FileIOItem workingIoItem;
|
protected FileIOContext workingIoContext;
|
protected int[] index;
|
protected IExcelValueTranslator[] translator;
|
protected int rowIndex = 1;
|
|
protected int percentStyleId;
|
protected int dateStyleId;
|
protected int dateTimeStyleId;
|
protected int numberFormatId;
|
|
|
public ExcelHandler(Processor progressor, Map<String, String> parameterProvider) {
|
columnReader = new ColumnReader();
|
numberFormatId = 254;
|
|
this.progressor = progressor;
|
this.parameterProvider = parameterProvider;
|
}
|
|
public void processFile(String file) {
|
sheetDocument = SpreadsheetDocument.Open(file, true);
|
try {
|
workbookPart = sheetDocument.WorkbookPart;
|
try {
|
//1. style
|
styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
|
if (styleSheet == null) {
|
styleSheet = new Stylesheet();
|
}
|
|
createCellStyle();
|
|
//2. shared string
|
sharedStringTable = workbookPart.SharedStringTablePart.SharedStringTable;
|
if (sharedStringTable == null) {
|
sharedStringTable = new SharedStringsTable();
|
}
|
|
//3. sheets
|
sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
|
processSheets();
|
}
|
finally {
|
workbookPart.Workbook.Save();
|
}
|
}
|
finally {
|
sheetDocument.Close();
|
}
|
}
|
|
protected void processSheets() {
|
|
}
|
|
protected void writeOneSheet(Sheet sheet, EntitySet entitySet) {
|
rowIndex = workingIoItem.getToRowNo();
|
|
WorksheetPart sourceSheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.);
|
String sourceSheetPartId = workbookPart.GetIdOfPart(sourceSheetPart);
|
|
WorksheetPart destSheetPart = workbookPart.AddNewPart<WorksheetPart>();
|
|
reader = OpenXmlReader.Create(sourceSheetPart);
|
try {
|
writer = OpenXmlWriter.Create(destSheetPart);
|
try {
|
bool skip = false;
|
|
while (reader.Read()) {
|
Type elementType = reader.ElementType;
|
|
if (elementType == typeof(Row)) {
|
if (reader.IsStartElement) {
|
skip = true;
|
}
|
else if (reader.IsEndElement) {
|
skip = false;
|
}
|
|
continue;
|
}
|
|
if (skip) {
|
continue;
|
}
|
|
if (elementType == typeof(SheetData)) {
|
if (reader.IsEndElement) {
|
continue;
|
}
|
|
doWriteOneSheet(sourceSheetPart, entitySet);
|
}
|
else {
|
if (reader.IsStartElement) {
|
writer.WriteStartElement(reader);
|
}
|
else if (reader.IsEndElement) {
|
writer.WriteEndElement();
|
}
|
}
|
}
|
}
|
finally {
|
writer.Close();
|
writer = null;
|
}
|
}
|
finally {
|
reader.Close();
|
reader = null;
|
}
|
|
workbookPart.DeletePart(sourceSheetPart);
|
workbookPart.ChangeIdOfPart(destSheetPart, sourceSheetPartId);
|
}
|
|
protected void doWriteOneSheet(WorksheetPart worksheetPart, EntitySet entitySet) {
|
writer.WriteStartElement(new SheetData());
|
try {
|
//1. header
|
progressor.newPhase("HandleHeader", "处理标题");
|
writeHeader(worksheetPart);
|
progressor.endPhase();
|
|
//2. mapping
|
progressor.newPhase("CreateMapping", "创建Mapping");
|
createColumnMappings();
|
progressor.endPhase();
|
|
//3.
|
progressor.newPhase("WriteRows", "写入数据");
|
writeRows(entitySet);
|
progressor.endPhase();
|
}
|
finally {
|
writer.WriteEndElement();
|
}
|
}
|
|
protected void writeHeader(WorksheetPart worksheetPart) {
|
IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();
|
|
int pos = 1;
|
foreach (Row row in rows ) {
|
columnReader.read(row, sharedStringTable, cellFormats);
|
writer.WriteElement(row);
|
|
pos++;
|
if (pos >= rowIndex) {
|
break;
|
}
|
}
|
}
|
|
protected void createColumnMappings() {
|
//1. create mapping run time
|
List<string> excelColumns = columnReader.getColumnNames();
|
workingIoItem.createMappingRuntime(excelColumns, workingIoContext);
|
|
//2. init index and translator
|
index = new int[excelColumns.Count];
|
translator = new IExcelValueTranslator[excelColumns.Count];
|
|
IOMappingItemRuntimeList itemRuntimeList = workingIoItem.getInsertMappingRuntime();
|
|
int i = 0;
|
foreach (String column in excelColumns) {
|
if (!itemRuntimeList.contaisToField(column)) {
|
index[i++] = -1;
|
continue;
|
}
|
|
IOMappingItemRuntime itemRuntime = itemRuntimeList.getItemByToField(column);
|
index[i] = itemRuntime.getFromIndex();
|
translator[i] = Translator.createExcelValueTranslator(itemRuntime.getExportType());
|
|
i++;
|
}
|
}
|
|
public void writeRows(EntitySet entitySet) {
|
while (entitySet.hasNext()) {
|
Entity entity = entitySet.getCurrent();
|
WriteOneRow(entity);
|
}
|
|
progressor.appendMesage("共写入数据" + entitySet.size() + "行");
|
}
|
|
public void WriteOneRow(Entity entity) {
|
Row row = new Row();
|
row.RowIndex = new UInt32Value(rowIndex);
|
|
writer.WriteStartElement(row);
|
try {
|
int columnIndex = 0; int max = index.Length;
|
|
for (int i = 0; i < max; i++) {
|
int idx = index[i];
|
|
if (idx < 0) {
|
continue;
|
}
|
|
Object value = entity.getObjectByIndex(idx);
|
writeOneCell(i, translator[i], value);
|
|
columnIndex++;
|
}
|
}
|
finally {
|
writer.WriteEndElement();
|
}
|
|
rowIndex++;
|
}
|
|
public void writeOneCell(int columnIndex, IExcelValueTranslator translator, Object data) {
|
Cell cell = new Cell();
|
cell.CellReference = toCellReference(columnIndex) + rowIndex;
|
|
if (data == null || data == DBNull.Value) {
|
writer.WriteElement(cell);
|
return;
|
}
|
|
CellDataType type = CellDataType.Error;
|
String value = translator.toString(ref type, data);
|
|
if (CellDataType.Boolean == type) {
|
WriteBoolean(cell, value);
|
}
|
else if (CellDataType.Int == type) {
|
WriteNumber(cell, value);
|
}
|
else if (CellDataType.Decimal == type) {
|
WriteNumber(cell, value);
|
}
|
else if (CellDataType.Percent == type) {
|
WritePercent(cell, value);
|
}
|
else if (CellDataType.String == type) {
|
WriteString(cell, value);
|
}
|
else if (CellDataType.Date == type) {
|
WriteDate(cell, value);
|
}
|
else if (CellDataType.DateTime == type) {
|
WriteDateTime(cell, value);
|
}
|
|
writer.WriteElement(cell);
|
}
|
|
protected void WriteBoolean(Cell cell, string value) {
|
cell.CellValue = new CellValue(value);
|
cell.DataType = new EnumValue<CellValues>(CellValues.Boolean);
|
}
|
|
protected void WriteNumber(Cell cell, string value) {
|
cell.CellValue = new CellValue(value);
|
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
|
}
|
|
protected void WritePercent(Cell cell, string value) {
|
cell.CellValue = new CellValue(value);
|
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
|
cell.StyleIndex = percentStyleId;
|
}
|
protected void WriteDate(Cell cell, string value) {
|
cell.CellValue = new CellValue(value);
|
cell.StyleIndex = dateStyleId;
|
}
|
|
protected void WriteDateTime(Cell cell, string value) {
|
cell.CellValue = new CellValue(value);
|
cell.StyleIndex = dateStyleId;
|
}
|
|
protected void WriteString(Cell cell, string value) {
|
cell.CellValue = new CellValue(value);
|
cell.DataType = new EnumValue<CellValues>(CellValues.String);
|
}
|
|
protected void WriteSharedString(Cell cell, string value) {
|
int stringId = InsertSharedStringItem(value);
|
cell.CellValue = new CellValue(stringId.ToString());
|
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
|
}
|
|
protected void WriteInlineString(Cell cell, string value) {
|
InlineString inlineString = new InlineString();
|
Text cellValueText = new Text { Text = value };
|
inlineString.AppendChild(cellValueText);
|
cell.Append(inlineString);
|
cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);
|
}
|
|
protected int InsertSharedStringItem(string text) {
|
int i = 0;
|
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
|
foreach (SharedStringItem item in sharedStringTable.Elements<SharedStringItem>()) {
|
if (item.InnerText == text) {
|
return i;
|
}
|
|
i++;
|
}
|
|
// The text does not exist in the part. Create the SharedStringItem and return its index.
|
sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
|
sharedStringTable.Save();
|
return i;
|
}
|
|
protected string toCellReference(int columnIndex) {
|
// start form 0;
|
string result = "";
|
|
int num = columnIndex;
|
do {
|
int i = num % 26;
|
result = alphabet[i] + result;
|
num = num / 26 - 1;
|
}
|
while (num > -1);
|
|
if (string.IsNullOrEmpty(result)) {
|
result = "A";
|
}
|
|
return result;
|
}
|
|
protected Sheet getSheetByName(String sheetname) {
|
IEnumerable<Sheet> sheetList = sheets.Elements<Sheet>();
|
Sheet sheet = null;
|
|
//1. sheet name is null
|
if (string.IsNullOrEmpty(sheetname)) {
|
sheet = sheetList.First<Sheet>();
|
}
|
|
if (sheet != null) {
|
return sheet;
|
}
|
|
sheetname = sheetname.ToLower();
|
|
//2. locate by name
|
sheet = sheetList.FirstOrDefault<Sheet>(s => s.Name.Value.ToLower().Equals(sheetname));
|
|
if (sheet != null) {
|
return sheet;
|
}
|
|
//3.
|
sheet = sheetList.First<Sheet>();
|
|
if (sheet != null) {
|
return sheet;
|
}
|
|
return null;
|
}
|
|
protected void createCellStyle() {
|
//1. create style
|
percentStyleId = createPercentStyleFormat();
|
dateStyleId = createDateStyleFormat();
|
dateTimeStyleId = createDateTimeStyleFormat();
|
|
//2. save
|
styleSheet.Save();
|
}
|
|
protected UInt32Value createPercentStyleFormat() {
|
CellFormat cellFormat = new CellFormat();
|
cellFormat.NumberFormatId = 10;
|
cellFormat.ApplyNumberFormat = true;
|
|
styleSheet.CellFormats.Append(cellFormat);
|
|
UInt32Value result = styleSheet.CellFormats.Count;
|
styleSheet.CellFormats.Count++;
|
return result;
|
}
|
|
protected UInt32Value createDateStyleFormat() {
|
//1. add number fromat
|
NumberingFormats numberingFormats = styleSheet.NumberingFormats;
|
if (numberingFormats == null) {
|
numberingFormats = new NumberingFormats();
|
}
|
|
NumberingFormat numberingFormat = new NumberingFormat();
|
numberingFormat.NumberFormatId = numberFormatId++;
|
numberingFormat.FormatCode = "yyyy\\-mm\\-dd";
|
|
numberingFormats.Append(numberingFormat);
|
|
//2. add cell fromat
|
CellFormat cellFormat = new CellFormat();
|
cellFormat.NumberFormatId = numberingFormat.NumberFormatId;
|
cellFormat.ApplyNumberFormat = true;
|
|
styleSheet.CellFormats.Append(cellFormat);
|
|
UInt32Value result = styleSheet.CellFormats.Count;
|
styleSheet.CellFormats.Count++;
|
return result;
|
}
|
|
protected UInt32Value createDateTimeStyleFormat() {
|
//1. add number fromat
|
NumberingFormats numberingFormats = styleSheet.NumberingFormats;
|
if (numberingFormats == null) {
|
numberingFormats = new NumberingFormats();
|
}
|
|
NumberingFormat numberingFormat = new NumberingFormat();
|
numberingFormat.NumberFormatId = numberFormatId++;
|
numberingFormat.FormatCode = "yyyy\\-mm\\-dd hh:mm:ss";
|
|
numberingFormats.Append(numberingFormat);
|
|
//2. add cell fromat
|
CellFormat cellFormat = new CellFormat();
|
cellFormat.NumberFormatId = numberingFormat.NumberFormatId;
|
cellFormat.ApplyNumberFormat = true;
|
|
styleSheet.CellFormats.Append(cellFormat);
|
|
UInt32Value result = styleSheet.CellFormats.Count;
|
styleSheet.CellFormats.Count++;
|
return result;
|
}
|
}
|
|
}
|
