C# 将数据表导出到具有三个工作表的 .xlsx 文件

Question

我在数据表中有 SQL 数据行，目前有以下代码输出到 .csv 文件中。我需要创建一个带有三个选项卡的 excel 文件。第一个选项卡需要是具有三个标题的空白工作表。中间选项卡需要是数据表的输出。最后一个选项卡需要是另一个带有另外三个标题的空白工作表。如何使用三个选项卡自动创建一个 Excel 工作簿，中间选项卡填充数据表输出。

 StringBuilder sb = new StringBuilder();

            IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
                                              Select(column => column.ColumnName);
            sb.AppendLine(string.Join(",", columnNames));

            foreach (DataRow row in dt.Rows)
            {
                IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
                sb.AppendLine(string.Join(",", fields));
            }

            // Specify a "currently active folder"
            string activeDir = @"C:\Users\Roger\Documents\Price_Files";

            string foldername = "PriceFile_" + DateTime.Today.ToString("yyyyMMdd");

            //Create a new subfolder under the current active folder
            string newPath = System.IO.Path.Combine(activeDir, foldername);

            // Create the subfolder
            System.IO.Directory.CreateDirectory(newPath);

            string filename = newPath + "\\" + "PriceFile_" + DateTime.Today.ToString("yyyyMMdd") + "_Retail_" + jurisdiction;

            File.WriteAllText(filename + ".csv", sb.ToString());

Answer 1

使用 EPPlus 库

                 using (ExcelPackage excel = new ExcelPackage())
                {
                    excel.Workbook.Worksheets.Add("Tab1"); // Create first tab
                    excel.Workbook.Worksheets.Add("Tab2");//Create second tab
                    excel.Workbook.Worksheets.Add("Tab3");//Create third tab
                    var excelWorksheet = excel.Workbook.Worksheets["Tab2"];
                    //Set value for 1 cell in 1 row in Tab2
                    excelWorksheet.Cells[1, 1].Value = "Some text";
                    //Simple aligment and fond for this cell
                    excelWorksheet.Cells[1, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    excelWorksheet.Cells[1, 1].Style.Font.Bold = true;
                    //adding data to cells from dataTable in the loop
                    foreach (DataRow row in dataTable)
                    {
                        excelWorksheet.Cells[position, 1].Value = row["*column_name*"].ToString();
                    }


                }

或者，您可以通过调用 LoadFromDataTable 方法来加载所有数据表，而不是在循环中设置数据

excelWorksheet.Cells[1, 1].LoadFromDataTable(dataTable, true);

最后调用 excel.GetAsByteArray() 将您的文件作为字节数组或调用
excel.SaveAs(...) 将其保存在您的硬盘上