C# 导出 Excel 的 6 种简单方法！你会几种？

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

value = instance.ImportDataTable(dataTable, firstRow, firstColumn, importOnSave);

value = instance.ImportDataTable(dataTable, namedRange, showColumnName, rowOffset, colOffset);

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

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

#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”); }

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

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