如何将数据表数据导出到现有的Excel工作表文件 [英] How to export datatable data to existing excel sheet file

问题描述

我想知道一种使用C＃和 OpenXml 导出数据表并将其附加到现有excel文件的方法。我正在生成产品报告表并在保存新数据时遇到问题。成功添加新行后，当我再次读取Excel或打开该特定Excel文件时，我无法在文件中找到新插入的行。 （表示数据写得不正确）。



如果你能为此提供示例代码，对我来说非常有用。

谢谢你提前。



我的尝试：

 私有 静态  void  AddRow （SpreadsheetDocument spreadSheetDoc，DataTable表）
 {
 workbookPart = spreadSheetDocument.WorkbookPart; 
 IEnumerable< sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild< sheets>（）。Elements< sheet>（）; 
 
  string  relationshipId = sheets.Where（s = >  s.Name == sheetName）.SingleOrDefault< sheet>（）。Id.Value; 
 
 worksheetPart =（WorksheetPart）spreadSheetDocument.WorkbookPart.GetPartById（relationshipId）; 
 workSheet = worksheetPart.Worksheet; 
 sheetData = workSheet.GetFirstChild< sheetdata>（）; 
 
 lastRow = sheetData.ChildElements.Count  -   1 ; 
 
  var  sheet = workbookPart.Workbook.Descendants< sheet>（）。FirstOrDefault（）; 
 
  if （sheet ==  null ）
  throw   new 异常（ 在模板文件中找不到任何杂项。请添加表单）; 
 
  int  rowIndex = lastRow +  1 ，colIndex =  0 ; 
 
 List< string> columns =  new  List< string>（）; 
 
  foreach （System.Data.DataColumn列 in  table.Columns）
 {
 columns.Add（column.ColumnName）; 
} 
 
  foreach （System.Data.DataRow dsrow  in  table.Rows）
 {
 Row row =  new  Row（）; 
  foreach （字符串 col  in 列）
 {
 DocumentFormat.OpenXml.Spreadsheet.Cell cell =  new  Cell（）; 
 cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues。 String ; 
 cell.CellValue =  new  DocumentFormat.OpenXml.Spreadsheet.CellValue（dsrow [col] .ToString（））; 
 row.AppendChild< cell>（cell）; 
} 
 sheetData.InsertAt< row>（row，rowIndex）; 
} 
 workbookPart.Workbook.Save（）; 
} 

解决方案

c＃ - 如何将数据表数据导出到现有的Excel工作表文件 - Stack Overflow [ ^ ]

I have wondering for a way to export and append data-table to existing excel file using C# and OpenXml. I am generating product report sheets and getting problem in Saving that new data. After append a new row successfully, when I read Excel again or Open that particular Excel file I cannot find newly inserted row in file. (means data is not properly written).

If you can provide sample code for this,Its very useful for me.
Thanks In Advance.

What I have tried:

private static void AddRow(SpreadsheetDocument spreadSheetDoc, DataTable table)
        {
            workbookPart = spreadSheetDocument.WorkbookPart;
            IEnumerable<sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<sheets>().Elements<sheet>();
            
            string relationshipId = sheets.Where(s => s.Name == sheetName).SingleOrDefault<sheet>().Id.Value;

            worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
            workSheet = worksheetPart.Worksheet;
            sheetData = workSheet.GetFirstChild<sheetdata>();

            lastRow = sheetData.ChildElements.Count - 1;

            var sheet = workbookPart.Workbook.Descendants<sheet>().FirstOrDefault();

            if (sheet == null)
                throw new Exception("No sheed found in the template file. Please add the sheet");

            int rowIndex = lastRow + 1, colIndex = 0;

            List<string> columns = new List<string>();

            foreach (System.Data.DataColumn column in table.Columns)
            {
                columns.Add(column.ColumnName);
            }

            foreach (System.Data.DataRow dsrow in table.Rows)
            {
                Row row = new Row();
                foreach (String col in columns)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new Cell();
                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
                    row.AppendChild<cell>(cell);
                }
                sheetData.InsertAt<row>(row, rowIndex);
            }
            workbookPart.Workbook.Save();
        }

解决方案

c# - How to export datatable data to existing excel sheet file - Stack Overflow[^]

这篇关于如何将数据表数据导出到现有的Excel工作表文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！