怎么在C#中導(dǎo)出Excel文件？針對這個問題，這篇文章詳細介紹了相對應(yīng)的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
成都創(chuàng)新互聯(lián)公司公司2013年成立，先為木壘哈薩克等服務(wù)建站，木壘哈薩克等地企業(yè)，進行企業(yè)商務(wù)咨詢服務(wù)。為木壘哈薩克企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。
Syncfusion Excel (XlsIO) 庫是一個 .Net Excel 庫，它支持用戶用 C# 和 VB.NET 以一個非常簡易的方式，將各種數(shù)據(jù)源（如數(shù)據(jù)表，數(shù)組，對象集合，數(shù)據(jù)庫，CSV / TSV，和微軟網(wǎng)格控件等）數(shù)據(jù)導(dǎo)出到 Excel 。
將數(shù)據(jù)導(dǎo)出到 Excel 可以以更容易理解的方式可視化數(shù)據(jù)。該特性有助于生成財務(wù)報告、銀行報表和發(fā)票，同時還支持篩選大數(shù)據(jù)、驗證數(shù)據(jù)、格式化數(shù)據(jù)等。
將數(shù)據(jù)導(dǎo)出到 Excel， Essential XlsIO 提供了以下方法:
數(shù)據(jù)表導(dǎo)出到 Excel
對象集合導(dǎo)出到 Excel
數(shù)據(jù)庫導(dǎo)出到 Excel
微軟網(wǎng)格控件導(dǎo)出到 Excel
數(shù)組導(dǎo)出到 Excel
CSV 導(dǎo)出到 Excel
在本文中，我們將研究這些方法以及如何執(zhí)行它們。
數(shù)據(jù)表導(dǎo)出到 Excel
ADO.NET 對象的數(shù)據(jù)(如 datatable 、datacolumn 和 dataview )可以導(dǎo)出到Excel 工作表。通過識別列類型或單元格值類型、超鏈接和大型數(shù)據(jù)集,可以在幾秒鐘內(nèi)將其導(dǎo)出并作為列標頭。
將數(shù)據(jù)表導(dǎo)出到 Excel 工作表可以通過 ImportDataTable 方法實現(xiàn)。下面的代碼示例演示了如何將員工詳細信息的數(shù)據(jù)表導(dǎo)出到 Excel 工作表。
重慶分公司
重慶分公司
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Create a new workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Create a dataset from XML file
DataSet customersDataSet = new DataSet();
customersDataSet.ReadXml(Path.GetFullPath(@"../../Data/Employees.xml"));
//Create datatable from the dataset
DataTable dataTable = new DataTable();
dataTable = customersDataSet.Tables[0];
//Import data from the data table with column header, at first row and first column,
//and by its column type.
sheet.ImportDataTable(dataTable, true, 1, 1, true);
//Creating Excel table or list object and apply style to the table
IListObject table = sheet.ListObjects.Create("Employee_PersonalDetails", sheet.UsedRange);
table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium14;
//Autofit the columns
sheet.UsedRange.AutofitColumns();
//Save the file in the given path
Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
workbook.SaveAs(excelStream);
excelStream.Dispose();
}
將數(shù)據(jù)表輸出到Excel
在將大數(shù)據(jù)導(dǎo)出到 Excel 時，如果不需要應(yīng)用數(shù)字格式和樣式，可以將其中importOnSave 參數(shù)的值設(shè)為 TRUE，使用 ImportDataTable 方法重載。此時，導(dǎo)出數(shù)據(jù)與保存 Excel 文件是同時進行的。
使用此方法導(dǎo)出高性能的大數(shù)據(jù)。
value = instance.ImportDataTable(dataTable, firstRow, firstColumn, importOnSave);
如果你有指定范圍，并且希望將數(shù)據(jù)從指定范圍的特定行和列導(dǎo)出到指定范圍，那么可以使用下面的 API，其中 rowOffset 和 columnOffset 是要從指定范圍中的特定單元導(dǎo)入的參數(shù)。
value = instance.ImportDataTable(dataTable, namedRange, showColumnName, rowOffset, colOffset);
對象集合導(dǎo)出到 Excel
將對象集合中的數(shù)據(jù)導(dǎo)出到 Excel 工作表是常見的場景。但是，如果需要將數(shù)據(jù)從模板導(dǎo)出到 Excel 工作表，這個方法將非常有用。
Syncfusion Excel (XlsIO) 庫支持將對象集合中的數(shù)據(jù)導(dǎo)出到 Excel 工作表。
我們可以通過 ImportData 方法將對象集合中的數(shù)據(jù)導(dǎo)出到 Excel 工作表。下面的代碼示例演示了如何將數(shù)據(jù)從集合導(dǎo)出到 Excel 工作表。
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Read the data from XML file
StreamReader reader = new StreamReader(Path.GetFullPath(@"../../Data/Customers.xml"));
//Assign the data to the customerObjects collection
IEnumerable customerObjects = GetData (reader.ReadToEnd());
//Create a new workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Import data from customerObjects collection
sheet.ImportData(customerObjects, 5, 1, false);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = Color.FromArgb(0, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
#endregion
#region Apply Styles
//Apply style to the header
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A1:D1"].Merge();
sheet["A2:D2"].Merge();
sheet["A3:A4"].Merge();
sheet["D3:D4"].Merge();
sheet["B3:C3"].Merge();
sheet["B3"].Text = "Sales";
sheet["A3"].Text = "Sales Person";
sheet["B4"].Text = "January - June";
sheet["C4"].Text = "July - December";
sheet["D3"].Text = "Change(%)";
sheet["A3:D4"].CellStyle = tableHeader;
sheet.UsedRange.AutofitColumns();
sheet.Columns[0].ColumnWidth = 24;
sheet.Columns[1].ColumnWidth = 21;
sheet.Columns[2].ColumnWidth = 21;
sheet.Columns[3].ColumnWidth = 16;
#endregion
sheet.UsedRange.AutofitColumns();
//Save the file in the given path
Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
workbook.SaveAs(excelStream);
excelStream.Dispose();
}
將對象集合輸出到Excel
數(shù)據(jù)庫導(dǎo)出到 Excel
Excel 支持從不同的數(shù)據(jù)庫創(chuàng)建 Excel 表。如果你需要使用 Excel 從數(shù)據(jù)庫創(chuàng)建一個或多個 Excel 表，那么需要逐個建立連接來創(chuàng)建。這可能很耗費時間。所以，如果能找到一種從數(shù)據(jù)庫快速、輕松地生成 Excel 表的替代方法，這難道不是首選方法嗎?
Syncfusion Excel (XlsIO) 庫可以將數(shù)據(jù)從 MS SQL 、MS Access 、Oracle 等數(shù)據(jù)庫導(dǎo)出到 Excel 工作表。通過在數(shù)據(jù)庫和 Excel 應(yīng)用程序之間建立連接，可以將數(shù)據(jù)從數(shù)據(jù)庫導(dǎo)出到 Excel 表。
可以使用 Refresh() 方法更新映射到數(shù)據(jù)庫的 Excel 表中的修改數(shù)據(jù)。
最重要的是，你可以參考文檔從外部連接創(chuàng)建一個表，以了解如何將數(shù)據(jù)庫導(dǎo)出到Excel 表。下面的代碼示例演示了如何將數(shù)據(jù)從數(shù)據(jù)庫導(dǎo)出到 Excel 表。
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Create a new workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
if(sheet.ListObjects.Count == 0)
{
//Estabilishing the connection in the worksheet
string dBPath = Path.GetFullPath(@"../../Data/EmployeeData.mdb");
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source="+ dBPath;
string query = "SELECT EmployeeID,FirstName,LastName,Title,HireDate,Extension,ReportsTo FROM [Employees]";
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, query, ExcelCommandType.Sql);
sheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, sheet.Range["A1"]);
}
//Refresh Excel table to get updated values from database
sheet.ListObjects[0].Refresh();
sheet.UsedRange.AutofitColumns();
//Save the file in the given path
Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
workbook.SaveAs(excelStream);
excelStream.Dispose();
}
將數(shù)據(jù)庫輸出到Excel表
將數(shù)據(jù)從 DataGrid 、GridView 、DataGridView 導(dǎo)出到 Excel
從微軟網(wǎng)格控件導(dǎo)出數(shù)據(jù)到 Excel 工作表，有助于以不同的方式可視化數(shù)據(jù)。你可能要花費數(shù)小時從網(wǎng)格單元格中遍歷其數(shù)據(jù)及其樣式，以便將它們導(dǎo)出到 Excel 工作表。對于那些需要將數(shù)據(jù)從微軟網(wǎng)格控件導(dǎo)出到 Excel 工作表的人來說，這應(yīng)該是個好消息，因為使用 Syncfusion Excel 庫導(dǎo)出要快得多。
Syncfusion Excel (XlsIO) 庫支持通過調(diào)用一個 API，將來自微軟網(wǎng)格控件(如DataGrid 、GridView 和 DataGridView )的數(shù)據(jù)導(dǎo)出到 Excel 工作表。此外，你還可以使用標題和樣式導(dǎo)出數(shù)據(jù)。
下面的代碼示例演示了如何將數(shù)據(jù)從 DataGridView 導(dǎo)出到 Excel 工作表。
#region Loading the data to DataGridView
DataSet customersDataSet = new DataSet();
//Read the XML file with data
string inputXmlPath = Path.GetFullPath(@"../../Data/Employees.xml");
customersDataSet.ReadXml(inputXmlPath);
DataTable dataTable = new DataTable();
//Copy the structure and data of the table
dataTable = customersDataSet.Tables[1].Copy();
//Removing unwanted columns
dataTable.Columns.RemoveAt(0);
dataTable.Columns.RemoveAt(10);
this.dataGridView1.DataSource = dataTable;
dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White;
dataGridView1.RowsDefaultCellStyle.BackColor = Color.LightBlue;
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new System.Drawing.Font("Tahoma", 9F, ((System.Drawing.FontStyle)(System.Drawing.FontStyle.Bold)));
dataGridView1.ForeColor = Color.Black;
dataGridView1.BorderStyle = BorderStyle.None;
#endregion
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
//Create a workbook with single worksheet
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Import from DataGridView to worksheet
worksheet.ImportDataGridView(dataGridView1, 1, 1, isImportHeader: true, isImportStyle: true);
worksheet.UsedRange.AutofitColumns();
workbook.SaveAs("Output.xlsx");
}
Microsoft DataGridView到Excel
數(shù)組導(dǎo)出到 Excel
有時，可能需要將數(shù)據(jù)數(shù)組插入或修改到 Excel 工作表中的現(xiàn)有數(shù)據(jù)中。在這種情況下，行數(shù)和列數(shù)是預(yù)先知道的。數(shù)組在固定范圍時非常有用。
Syncfusion Excel (XlsIO) 庫支持將數(shù)據(jù)數(shù)組導(dǎo)出到 Excel 工作表中，水平方向和垂直方向?qū)С鼍?。此外，還可以導(dǎo)出二維數(shù)組。
讓我們考慮一個場景，“人均開支”。一個人全年的花費都列在 Excel 工作表中。在這個場景中，你需要在新建一行，添加一個新用戶 Paul Pogba 的開銷，并更新所有被跟蹤人員 12 月的開銷。
從數(shù)組導(dǎo)出前的 Excel 數(shù)據(jù)
可以通過 ImportArray 方法將數(shù)據(jù)數(shù)組導(dǎo)出到 Excel 工作表。下面的代碼示例演示了如何將數(shù)據(jù)數(shù)組導(dǎo)出到 Excel 工作表中，水平方向和垂直方向都是如此。
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Reads input Excel stream as a workbook
IWorkbook workbook = application.Workbooks.Open(File.OpenRead(Path.GetFullPath(@"../../../Expenses.xlsx")));
IWorksheet sheet = workbook.Worksheets[0];
//Preparing first array with different data types
object[] expenseArray = new object[14]
{"Paul Pogba", 469.00d, 263.00d, 131.00d, 139.00d, 474.00d, 253.00d, 467.00d, 142.00d, 417.00d, 324.00d, 328.00d, 497.00d, "=SUM(B11:M11)"};
//Inserting a new row by formatting as a previous row.
sheet.InsertRow(11, 1, ExcelInsertOptions.FormatAsBefore);
//Import Peter's expenses and fill it horizontally
sheet.ImportArray(expenseArray, 11, 1, false);
//Preparing second array with double data type
double[] expensesOnDec = new double[6]
{179.00d, 298.00d, 484.00d, 145.00d, 20.00d, 497.00d};
//Modify the December month's expenses and import it vertically
sheet.ImportArray(expensesOnDec, 6, 13, true);
//Save the file in the given path
Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
workbook.SaveAs(excelStream);
excelStream.Dispose();
}
將數(shù)據(jù)數(shù)組輸出到Excel
CSV 導(dǎo)出到 Excel
逗號分隔值 (CSV) 文件有助于生成列數(shù)少、行數(shù)多的表格數(shù)據(jù)或輕量級報告。Excel 格式打開這些文件，更容易讀懂?dāng)?shù)據(jù)。
Syncfusion Excel (XlsIO) 庫支持在幾秒鐘內(nèi)打開和保存 CSV 文件。下面的代碼示例演示了如何打開 CSV 文件，并將其保存為 XLSX 文件。最重要的是，數(shù)據(jù)顯示在數(shù)字格式的表格中。
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Preserve data types as per the value
application.PreserveCSVDataTypes = true;
//Read the CSV file
Stream csvStream = File.OpenRead(Path.GetFullPath(@"../../../TemplateSales.csv")); ;
//Reads CSV stream as a workbook
IWorkbook workbook = application.Workbooks.Open(csvStream);
IWorksheet sheet = workbook.Worksheets[0];
//Formatting the CSV data as a Table
IListObject table = sheet.ListObjects.Create("SalesTable", sheet.UsedRange);
table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium6;
IRange location = table.Location;
location.AutofitColumns();
//Apply the proper latitude & longitude numerformat in the table
TryAndUpdateGeoLocation(table,"Latitude");
TryAndUpdateGeoLocation(table,"Longitude");
//Apply currency numberformat in the table column 'Price'
IRange columnRange = GetListObjectColumnRange(table,"Price");
if(columnRange != null)
columnRange.CellStyle.NumberFormat = "$#,##0.00";
//Apply Date time numberformat in the table column 'Transaction_date'
columnRange = GetListObjectColumnRange(table,"Transaction_date");
if(columnRange != null)
columnRange.CellStyle.NumberFormat = "m/d/yy h:mm AM/PM;@";
//Sort the data based on 'Products'
IDataSort sorter = table.AutoFilters.DataSorter;
ISortField sortField = sorter. SortFields. Add(0, SortOn. Values, OrderBy. Ascending);
sorter. Sort();
//Save the file in the given path
Stream excelStream;
excelStream = File.Create(Path.GetFullPath(@"../../../Output.xlsx"));
workbook.SaveAs(excelStream);
excelStream.Dispose();
}
輸入csv文件
關(guān)于怎么在C#中導(dǎo)出Excel文件問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識。
