关闭

Open Xml 创建Excel并插入数据

340人阅读 评论(0) 收藏 举报
分类:
private static void CreateSpreadSheet()
        {
            string fileName = "E:\\01.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;
        }
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:77520次
    • 积分:1087
    • 等级:
    • 排名:千里之外
    • 原创:10篇
    • 转载:106篇
    • 译文:0篇
    • 评论:8条
    最新评论