openxml追加写入已存在的xlsx文件

最新推荐文章于 2023-09-24 10:20:42 发布

qq_42678477

最新推荐文章于 2023-09-24 10:20:42 发布

阅读量430

点赞数

CC 4.0 BY-SA版权

分类专栏： C#

本文链接：https://blog.csdn.net/qq_42678477/article/details/107848978

C# 专栏收录该内容

20 篇文章

订阅专栏

本文介绍了一种使用C#将Excel数据导入到DataTable的方法，并提供了详细的代码实现，包括如何处理不同类型的数据，如整数、浮点数、字符串和日期等。

        private void m2(string fname, string sheetName, DataTable dt, Func<DataTable, DataTable> dealer = null)
        {
            if (null != dealer)
                dt = dealer(dt);

            //string sheetName = "Data1";
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fname, true))
            {
                IEnumerable<Sheet> sheets = doc.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                    return;

                WorksheetPart wsPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets.First().Id);
                Worksheet worksheet = wsPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();


                Row row = null;
                {
                    row = new Row();
                    Cell cell = null;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        cell = new Cell();
                        cell.DataType = CellValues.String;
                        cell.CellValue = new CellValue(dt.Columns[i].ColumnName);
                        row.AppendChild(cell);
                    }
                    sheetData.AppendChild(row);

                }

                foreach (DataRow dr in dt.Rows)
                {
                    row = new Row();
                    Cell cell = null;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        cell = new Cell();
                        cell.DataType = GetCellValueType(dr[i]);
                        cell.CellValue = new CellValue(dr[i].ToString());
                        row.AppendChild(cell);
                    }
                    sheetData.AppendChild(row);
                }

                worksheet.Save();
                doc.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
                doc.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
                doc.WorkbookPart.Workbook.Save();
                doc.Close();
            }
            //MyCommon.ExecuteProcess("explorer.exe", fname);
        }


        private CellValues GetCellValueType(object obj)
        {
            switch (obj.GetType().Name)
            {
                case "Int32":
                case "Int64":
                case "Decimal":
                case "Double":
                    return CellValues.Number;
                case "DateTime":
                case "String":
                    return CellValues.String;
                default:
                    return CellValues.String;
            }
        }