Open Xml 创建Excel并插入数据
发布时间：2020-12-16 09:04:03 所属栏目：百科 来源：网络整理
导读：private static void CreateSpreadSheet() { string fileName = "E: 1.xlsx"; string sheetName = "test"; using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileName,SpreadsheetDocumentType.Workbook)) { // create the workbook s
|
private static void CreateSpreadSheet() { string fileName = "E: 1.xlsx"; string sheetName = "test"; using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileName,SpreadsheetDocumentType.Workbook)) { // create the workbook spreadSheet.AddWorkbookPart(); spreadSheet.WorkbookPart.Workbook = new Workbook(); // create the worksheet spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>(); spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet(); SharedStringTablePart m_SharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>(); // create sheet data SheetData m_SheetData = new SheetData(); spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(m_SheetData); // create row //spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row()); //// create cell with data //spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(new Cell() { CellValue = new CellValue("100") }); //Add Data Row row1 = new Row() { RowIndex = 1 }; Cell cell1 = new Cell() { CellReference = "A1" }; int index = InsertSharedStringItem("Sun",m_SharedStringTablePart); cell1.CellValue = new CellValue(index.ToString()); cell1.DataType = new EnumValue<CellValues>(CellValues.SharedString); row1.Append(cell1); m_SheetData.Append(row1); // save worksheet spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save(); // create the worksheet to workbook relation spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets()); spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet() { Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),SheetId = 1,Name = sheetName }); spreadSheet.WorkbookPart.Workbook.Save(); } } private static int InsertSharedStringItem(string text,SharedStringTablePart shareStringPart) { // If the part does not contain a SharedStringTable,create one. if (shareStringPart.SharedStringTable == null) { shareStringPart.SharedStringTable = new SharedStringTable(); shareStringPart.SharedStringTable.Count = 1; shareStringPart.SharedStringTable.UniqueCount = 1; } int i = 0; // Iterate through all the items in the SharedStringTable. If the text already exists,return its index. foreach (SharedStringItem item in shareStringPart.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. shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text))); shareStringPart.SharedStringTable.Save(); return i; } （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |