CODEWITHSUNDEEP
c#excel
roa765
roa765

Reputation: 295

C# Export Data Table to .xlsx file with three worksheets

I have SQL data rows in a data table and currently have the below code that outputs into a .csv file. I need to create an excel file with three tabs. The first tab needs to be a blank worksheet with three headers. The middle tab needs to be the output of the data table. The final tab needs to be another blank worksheet with another three headers. How do I create an excel workbook automatically with three tabs and the middle tab populated with the data table output.

 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());

Upvotes: 1

Views: 767

Answers (1)

DespeiL
DespeiL

Reputation: 1033

Using EPPlus library

                 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();
                    }


                }

Or instead of seting data in loop you may just load all dataTable by calling LoadFromDataTable method

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

At the end call excel.GetAsByteArray() to get your file as byte array or call excel.SaveAs(...) to save it psychically on your hdd

Upvotes: 3

Related Questions