/// <summary>
/// 打开一个文件
/// </summary>
/// <param name="file">要打开的文件路径</param>
/// <returns></returns>
public static XlsxFileReader Open(string file, string sheetName = "Sheet1")
{
XlsxFileReader fr = new XlsxFileReader();
SpreadsheetDocument tmpDoc = null;
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.ReadWrite))
{
tmpDoc = SpreadsheetDocument.Open(fs, true);
var workbookPart = tmpDoc.WorkbookPart;
var workBook = workbookPart.Workbook;
if (string.IsNullOrWhiteSpace(sheetName) == false)
{
if (workbookPart.Workbook.Sheets.FirstOrDefault(obj => (obj as Sheet).Name == sheetName) == null)
{
throw new Exception("指定的文件中不包含:" + sheetName + "文件:" + file);
}
}
foreach (Sheet sheet in tmpDoc.WorkbookPart.Workbook.Sheets)
{
var vvvvvvvvvvv = tmpDoc.WorkbookPart.GetPartById(sheet.Id);
var s = tmpDoc.WorkbookPart.GetPartById(sheet.Id) as WorksheetPart;
if (s == null)
{
throw new Exception("文件结构不对，找不表:" + sheet.Name + "的数据" + "文件:" + file);
}
//为空表示需要解析所有的表
if (string.IsNullOrWhiteSpace(sheetName) || sheetName.Equals(sheet.Name))
{
string[][] rows = ParseSheet(tmpDoc, s.Worksheet, sheet.Name);
fr.sheetDatas.Add(sheet.Name, rows);
}
}
}
return(fr);
}
public static void WriteXlsx(string file, string[][] contents)
{
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(file, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
var ssp = workbookpart.AddNewPart <SharedStringTablePart>();
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1"
};
sheets.Append(sheet);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
uint rowCount = 1;
//加入所有行
foreach (var contentRow in contents)
{
Row row = new Row {
RowIndex = rowCount, Spans = new ListValue <StringValue>()
};
row.Spans.Items.Add(new StringValue("1:" + contentRow.Length.ToString()));
for (int i = 0; i < contentRow.Length; i++)
{
if (contentRow[i] == null)
{
continue;
}
Cell cell = new Cell {
CellReference = XlsxFileReader.GetExcelColumnName(i + 1) + rowCount
};
if (contentRow[i].All(obj => Char.IsDigit(obj) || obj == '.'))
{
cell.CellValue = new CellValue(contentRow[i]);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
}
else
{
cell.CellValue = new CellValue(InsertSharedStringItem(contentRow[i], ssp).ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
row.AppendChild(cell);
}
sheetData.AppendChild(row);
rowCount++;
}
spreadsheetDocument.Close();
}
