vsto openxml快速写入指定格子值

最新推荐文章于 2024-02-02 18:10:34 发布

BugLovers

最新推荐文章于 2024-02-02 18:10:34 发布

阅读量132

点赞数

分类专栏： vsto 文章标签： vsto Powered by 金山文档

本文链接：https://blog.csdn.net/nbspzs/article/details/129365104

版权

vsto 专栏收录该内容

108 篇文章 8 订阅

订阅专栏

private static void WriteToExcel(string[] contents, char[] desiredColumns)
{
    string filePath = @"D:\\Folder\\test.xlsx";
    try
    {
        using (SpreadsheetDocument StatsCollection = SpreadsheetDocument.Open(filePath, true))
        {
            WorkbookPart bookPart = StatsCollection.WorkbookPart;
            string sheetName ="Sheet1";
            SharedStringTablePart sstpart = bookPart.GetPartsOfType<SharedStringTablePart>().First();
            SharedStringTable sst = sstpart.SharedStringTable;
            Sheet mySheet = bookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
            WorksheetPart sheetPart = (WorksheetPart)(bookPart.GetPartById(mySheet.Id));
            Worksheet sheet = sheetPart.Worksheet;
            var rows = sheet.Descendants<Row>();
            foreach (Row row in rows)
            {
                foreach (Cell currentCell in row.Elements<Cell>())
                {
                    int totalRows = contents.Length / 15;
                    int contentsIndex = 0;
                    for (uint indexRow = 1; indexRow <= totalRows; indexRow++)
                    {
                        for (int indexCol = 1; indexCol < 16; indexCol++)
                        {
                            SharedStringTablePart shareStringPart;
                            if (StatsCollection.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                            {
                                shareStringPart = StatsCollection.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                            }
                            else
                            {
                                shareStringPart = StatsCollection.WorkbookPart.AddNewPart<SharedStringTablePart>();
                            }
                            int index = InsertSharedStringItem(contents[contentsIndex], shareStringPart);
                            if (currentCell.CellValue.InnerText =="")
                            {
                                Cell newCell = InsertCellInWorksheet(desiredColumns[indexCol - 1].ToString(), indexRow, sheetPart);
                                if (IsNumeric(contents[contentsIndex]))
                                {
                                    newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
                                    newCell.CellValue = new CellValue(contents[contentsIndex]);
                                }
                                else
                                {
                                    newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                                    newCell.CellValue = new CellValue(index.ToString());
                                }
                            }
                            else
                            {
                                break;
                            }
                            sheetPart.Worksheet.Save();
                            contentsIndex++;
                        }
                    }
                }
            }
        }
        Console.WriteLine("Copy Complete.");
    }
    catch (Exception ex)
    {
        Console.WriteLine("Cannot find output Excel file.\
" + ex.Message);
    }
}

xls不支持openxml，xlsx支持openxml