EXPORT TO EXCEL C#

INTRODUCTION:

Trading information to Succeed utilizing C# includes utilizing libraries and APIs that smooth out the cycle, permitting engineers to zero in on the center usefulness of their applications. This capacity is especially significant while managing enormous datasets or while furnishing clients with the adaptability to examine and control information in a natural climate.

Sending out information to Succeed in C# is a principal necessity for some applications, working with effective information examination and detailing. Using libraries, for example, EPPlus or Microsoft's Open XML SDK, engineers can flawlessly produce Succeed documents with information from different sources. The cycle regularly includes making worksheets, populating cells with information, and designing arranging choices.

6 Simple Methods for trading Information to Succeed in C#:

from different information sources like information tables, datasets, exhibits, assortments of items, data sets, CSV/TSV, and Microsoft Lattice controls in an exceptionally straightforward and simple manner. Sending out information to Succeed helps in envisioning the information in a more justifiable design. This component assists with creating monetary reports, banking explanations, and solicitations, while additionally considering sifting huge information, approving information, arranging information, and that's only the tip of the iceberg.

Fundamental XlsIO gives the accompanying ways of sending out information to Succeed:

DataTable to Succeed
Assortment of objects to Succeed
Data set to Succeed
Microsoft Lattice controls to Succeed
Cluster to Succeed
CSV to Succeed

1. Send out from DataTable to Succeed

Information from ADO.NET items, for example, datatable, datacolumn, and dataview can be traded to Succeed worksheets. The trading should be possible as section headers by perceiving segment types or cell esteem types, as hyperlinks, and as enormous datasets, all in no time flat.

Sending out Data Table to Succeed worksheets can be accomplished through the Import Data Table technique. The accompanying code test tells the best way to send out a worker subtleties information table to a Succeed worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;

    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet sheet = workbook.Worksheets[0];

    DataSet customersDataSet = new DataSet();
    customersDataSet.ReadXml(Path.GetFullPath(@"../../Data/Employees.xml"));

    DataTable dataTable = new DataTable();
    dataTable = customersDataSet.Tables[0];

    //and by its column type.
    sheet.ImportDataTable(dataTable, true, 1, 1, true);

    IListObject table = sheet.ListObjects.Create("Employee_PersonalDetails", sheet.UsedRange);

    table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium14;

    sheet.UsedRange.AutofitColumns();

    Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

While sending out enormous information to Succeed, and in the event that there is compelling reason need to apply number organizations and styles, you can utilize the Import Data Table over-burden with the Genuine incentive for import On Save boundary. Here, the product occurs while saving the Succeed record.

Utilize this choice to send out enormous information with superior execution.

On the off chance that you have a named reach and like to trade information to a named range from a particular line and section of the named range, you can utilize the underneath Programming interface, where rowOffset and columnOffset are the boundaries to import from a specific cell in a named range.

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

2.Trade from assortment of objects to Succeed:

Sending out information from an assortment of objects to a Succeed worksheet is a typical situation. In any case, this component will be useful when you want to trade information from a model to a Succeed worksheet.

The Syncfusion Succeed (XlsIO) library offers help to send out information from an assortment of objects to a Succeed worksheet; this assortment can be either a solitary assortment or settled assortments. Trading information from settled assortments to a Succeed worksheet is useful in keeping up with the information order. Likewise, Syncfusion XlsIO permits to trade information with different format and gathering choices. The blog Product Information from Assortment to Succeed and Gathering It in C# plainly clears up how for trade information from assortment to Succeed worksheet and furthermore makes sense of the choices accessible while sending out.

Here, we should perceive how to trade information from an assortment of objects to a Succeed worksheet. This can be accomplished utilizing the ImportData strategy. The accompanying code model tells the best way to send out information from an assortment to a Succeed worksheet.

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

    
    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;
    #endregion

    sheet.UsedRange.AutofitColumns();

    
    Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

3. Trade from Information base to Succeed

Succeed upholds making Succeed tables from various information bases. In the event that you have a situation wherein you really want to make at least one Succeed tables from a data set utilizing Succeed, you want to lay out each and every association with make those tables. This can be tedious, so assuming you track down a substitute method for creating Succeed tables from the information base rapidly and effectively, couldn't that be your best option?

The Syncfusion Succeed (XlsIO) library assists you with trading information to Succeed worksheets from data sets like MS SQL, MS Access, Prophet, and that's only the tip of the iceberg. By laying out an association between the data sets and Succeed application, you can trade information from a data set to a Succeed table.

You can utilize the Revive() choice to refresh the changed information in the Succeed table that is planned to the data set.

Most importantly, you can allude to the documentation to make a table from an outer association with look into how to send out data sets to Succeed tables. The accompanying code test tells the best way to trade information from a data set to a Succeed table.

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 question = "SELECT EmployeeID,FirstName,LastName,Title,HireDate,Extension,ReportsTo FROM [Employees]";
        IConnection Association = workbook.Connections.Add("Connection1", "Test association with MsAccess", ConnectionString, inquiry, ExcelCommandType.Sql);
        sheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, sheet.Range["A1"]);
    }

    
    sheet.ListObjects[0].Refresh();

    sheet.UsedRange.AutofitColumns();
                    
    
    Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

4.Trade information from DataGrid, GridView, DataGridView to Succeed

Trading information from Microsoft framework controls to Succeed worksheets assists with picturing information in various ways. You might work for quite a long time to emphasize information and its styles from matrix cells to trade them into Succeed worksheets. It ought to be uplifting news for the people who send out information from Microsoft framework controls to Succeed worksheets, since trading with Syncfusion Succeed library is a lot quicker.

Syncfusion Succeed (XlsIO) library upholds trading information from Microsoft Framework controls, like DataGrid, GridView, and DataGridView, to Succeed worksheets in a solitary Programming interface call. Likewise, you can send out information with header and styles.

The accompanying code model tells the best way to trade information from DataGridView to a Succeed worksheet.

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

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];

   
    worksheet.ImportDataGridView(dataGridView1, 1, 1, isImportHeader: valid, isImportStyle: valid);

    worksheet.UsedRange.AutofitColumns();
    workbook.SaveAs("Output.xlsx");
}

5.Send out from exhibit to Succeed

Some of the time, there might be a need where a variety of information might should be embedded or changed into existing information in a Succeed worksheet. For this situation, the quantity of lines and sections are known ahead of time. Clusters are helpful when you have a decent size.

The Syncfusion NET Succeed library offers help to trade a variety of information into a Succeed worksheet, both on a level plane and in an upward direction. What's more, two-layered clusters can likewise be traded.

Allow us to consider a situation, "Costs per Individual." The costs of an individual for the entire year is organized in the Succeed worksheet. In this situation, you really want to add costs for a renewed individual, Paul Pogba, in another column and change the costs of all followed individuals for the month Dec.

Trading a variety of information to a Succeed worksheet can be accomplished through the Import Array strategy. The accompanying code test tells the best way to trade a variety of information to a Succeed worksheet, both on a level plane and in an upward direction.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;

    //Peruses input Succeed stream as an exercise manual
    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]
    //Inserting a new row by formatting as a previous row.
    sheet.InsertRow(11, 1, ExcelInsertOptions.FormatAsBefore);

//Import Peter's costs and fill it on a level plane    sheet.ImportArray(expenseArray, 11, 1, false);

   //Getting ready second exhibit with twofold information 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 record in the given way
    Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

6. Trade from CSV to Succeed

Comma-isolated esteem (CSV) records are useful in producing even information or lightweight reports with not many sections and countless lines. Succeed opens such documents to make the information more straightforward to peruse.

The Syncfusion .NET Succeed library upholds opening and saving CSV records in short order. The accompanying code model tells the best way to open a CSV record, likewise save it as a XLSX document. Most importantly, the information is displayed in a table with the quantity of organizations applied.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;

    //Save information types according to the worth
    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 legitimate scope and longitude numerformat in the table
    TryAndUpdateGeoLocation(table,"Latitude");
    TryAndUpdateGeoLocation(table,"Longitude");

    //Apply money numberformat in the table segment 'Cost'
    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 record in the given way
    Stream excelStream;
    excelStream = File.Create(Path.GetFullPath(@"../../../Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

Wrapping up

As may be obvious, Syncfusion .NET Succeed library gives different simple methods for sending out information to Succeed in C#. Use them successfully to create Succeed reports with elite execution and to handle huge information. Pause for a minute to examine the documentation, where you'll find different choices and highlights, all with going with code tests. Utilizing the library, you can likewise send out or compose Succeed information to PDF, picture, information table, CSV, TSV, HTML, assortments of items, ODS document arrangement, and that's only the tip of the iceberg.

Assuming you are new to our .NET Succeed library, it is strongly suggested that you follow our Beginning aide. To trade information in C#, pick a configuration (e.g., CSV, Succeed), utilize pertinent libraries (e.g., CsvHelper, EPPlus), plan information structures, oversee record I/O, consider UI, carry out blunder dealing with, lead careful testing, and give documentation. Model: CSV send out with CsvHelper includes setting up accounts to a predetermined document way.

Next TopicFinancial Functions in Excel

← prev next →