怎么在C#中導(dǎo)出Excel文件?針對(duì)這個(gè)問題,這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答,希望可以幫助更多想解決這個(gè)問題的小伙伴找到更簡(jiǎn)單易行的方法。
成都創(chuàng)新互聯(lián)公司專注于企業(yè)營(yíng)銷型網(wǎng)站、網(wǎng)站重做改版、鄠邑網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5網(wǎng)站設(shè)計(jì)、商城系統(tǒng)網(wǎng)站開發(fā)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù),價(jià)格優(yōu)惠性價(jià)比高,為鄠邑等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。Syncfusion Excel (XlsIO) 庫(kù)是一個(gè) .Net Excel 庫(kù),它支持用戶用 C# 和 VB.NET 以一個(gè)非常簡(jiǎn)易的方式,將各種數(shù)據(jù)源(如數(shù)據(jù)表,數(shù)組,對(duì)象集合,數(shù)據(jù)庫(kù),CSV / TSV,和微軟網(wǎng)格控件等)數(shù)據(jù)導(dǎo)出到 Excel 。
將數(shù)據(jù)導(dǎo)出到 Excel 可以以更容易理解的方式可視化數(shù)據(jù)。該特性有助于生成財(cái)務(wù)報(bào)告、銀行報(bào)表和發(fā)票,同時(shí)還支持篩選大數(shù)據(jù)、驗(yàn)證數(shù)據(jù)、格式化數(shù)據(jù)等。
將數(shù)據(jù)導(dǎo)出到 Excel, Essential XlsIO 提供了以下方法:
數(shù)據(jù)表導(dǎo)出到 Excel
對(duì)象集合導(dǎo)出到 Excel
數(shù)據(jù)庫(kù)導(dǎo)出到 Excel
微軟網(wǎng)格控件導(dǎo)出到 Excel
數(shù)組導(dǎo)出到 Excel
CSV 導(dǎo)出到 Excel
在本文中,我們將研究這些方法以及如何執(zhí)行它們。
數(shù)據(jù)表導(dǎo)出到 Excel
ADO.NET 對(duì)象的數(shù)據(jù)(如 datatable 、datacolumn 和 dataview )可以導(dǎo)出到Excel 工作表。通過識(shí)別列類型或單元格值類型、超鏈接和大型數(shù)據(jù)集,可以在幾秒鐘內(nèi)將其導(dǎo)出并作為列標(biāo)頭。
將數(shù)據(jù)表導(dǎo)出到 Excel 工作表可以通過 ImportDataTable 方法實(shí)現(xiàn)。下面的代碼示例演示了如何將員工詳細(xì)信息的數(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 時(shí),如果不需要應(yīng)用數(shù)字格式和樣式,可以將其中importOnSave 參數(shù)的值設(shè)為 TRUE,使用 ImportDataTable 方法重載。此時(shí),導(dǎo)出數(shù)據(jù)與保存 Excel 文件是同時(shí)進(jìn)行的。
使用此方法導(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);
對(duì)象集合導(dǎo)出到 Excel
將對(duì)象集合中的數(shù)據(jù)導(dǎo)出到 Excel 工作表是常見的場(chǎng)景。但是,如果需要將數(shù)據(jù)從模板導(dǎo)出到 Excel 工作表,這個(gè)方法將非常有用。
Syncfusion Excel (XlsIO) 庫(kù)支持將對(duì)象集合中的數(shù)據(jù)導(dǎo)出到 Excel 工作表。
我們可以通過 ImportData 方法將對(duì)象集合中的數(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(); }
將對(duì)象集合輸出到Excel
數(shù)據(jù)庫(kù)導(dǎo)出到 Excel
Excel 支持從不同的數(shù)據(jù)庫(kù)創(chuàng)建 Excel 表。如果你需要使用 Excel 從數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)或多個(gè) Excel 表,那么需要逐個(gè)建立連接來創(chuàng)建。這可能很耗費(fèi)時(shí)間。所以,如果能找到一種從數(shù)據(jù)庫(kù)快速、輕松地生成 Excel 表的替代方法,這難道不是選方法嗎?
Syncfusion Excel (XlsIO) 庫(kù)可以將數(shù)據(jù)從 MS SQL 、MS Access 、Oracle 等數(shù)據(jù)庫(kù)導(dǎo)出到 Excel 工作表。通過在數(shù)據(jù)庫(kù)和 Excel 應(yīng)用程序之間建立連接,可以將數(shù)據(jù)從數(shù)據(jù)庫(kù)導(dǎo)出到 Excel 表。
可以使用 Refresh() 方法更新映射到數(shù)據(jù)庫(kù)的 Excel 表中的修改數(shù)據(jù)。
最重要的是,你可以參考文檔從外部連接創(chuàng)建一個(gè)表,以了解如何將數(shù)據(jù)庫(kù)導(dǎo)出到Excel 表。下面的代碼示例演示了如何將數(shù)據(jù)從數(shù)據(jù)庫(kù)導(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ù)庫(kù)輸出到Excel表
將數(shù)據(jù)從 DataGrid 、GridView 、DataGridView 導(dǎo)出到 Excel
從微軟網(wǎng)格控件導(dǎo)出數(shù)據(jù)到 Excel 工作表,有助于以不同的方式可視化數(shù)據(jù)。你可能要花費(fèi)數(shù)小時(shí)從網(wǎng)格單元格中遍歷其數(shù)據(jù)及其樣式,以便將它們導(dǎo)出到 Excel 工作表。對(duì)于那些需要將數(shù)據(jù)從微軟網(wǎng)格控件導(dǎo)出到 Excel 工作表的人來說,這應(yīng)該是個(gè)好消息,因?yàn)槭褂?Syncfusion Excel 庫(kù)導(dǎo)出要快得多。
Syncfusion Excel (XlsIO) 庫(kù)支持通過調(diào)用一個(gè) API,將來自微軟網(wǎng)格控件(如DataGrid 、GridView 和 DataGridView )的數(shù)據(jù)導(dǎo)出到 Excel 工作表。此外,你還可以使用標(biāo)題和樣式導(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í),可能需要將數(shù)據(jù)數(shù)組插入或修改到 Excel 工作表中的現(xiàn)有數(shù)據(jù)中。在這種情況下,行數(shù)和列數(shù)是預(yù)先知道的。數(shù)組在固定范圍時(shí)非常有用。
Syncfusion Excel (XlsIO) 庫(kù)支持將數(shù)據(jù)數(shù)組導(dǎo)出到 Excel 工作表中,水平方向和垂直方向?qū)С鼍?。此外,還可以導(dǎo)出二維數(shù)組。
讓我們考慮一個(gè)場(chǎng)景,“人均開支”。一個(gè)人全年的花費(fèi)都列在 Excel 工作表中。在這個(gè)場(chǎng)景中,你需要在新建一行,添加一個(gè)新用戶 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
逗號(hào)分隔值 (CSV) 文件有助于生成列數(shù)少、行數(shù)多的表格數(shù)據(jù)或輕量級(jí)報(bào)告。Excel 格式打開這些文件,更容易讀懂?dāng)?shù)據(jù)。
Syncfusion Excel (XlsIO) 庫(kù)支持在幾秒鐘內(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)容可以對(duì)大家有一定的幫助,如果你還有很多疑惑沒有解開,可以關(guān)注創(chuàng)新互聯(lián)成都網(wǎng)站設(shè)計(jì)公司行業(yè)資訊頻道了解更多相關(guān)知識(shí)。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)scvps.cn,海內(nèi)外云服務(wù)器15元起步,三天無理由+7*72小時(shí)售后在線,公司持有idc許可證,提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案,具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì),專為企業(yè)上云打造定制,能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。
網(wǎng)站題目:怎么在C#中導(dǎo)出Excel文件-創(chuàng)新互聯(lián)
網(wǎng)站網(wǎng)址:http://www.muchs.cn/article22/dodicc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供建站公司、App設(shè)計(jì)、定制網(wǎng)站、搜索引擎優(yōu)化、軟件開發(fā)、App開發(fā)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)
猜你還喜歡下面的內(nèi)容