none
how to write a large amount data to an excel file using c# RRS feed

  • Question

  • i want to save a large amount of data almost 60 - 70 k lines ....

    i will be geting all these values from a list<someobj> . 

    is there any efficient way for this ??

    please help .

    THANK YOU .

    Wednesday, December 27, 2017 7:12 AM

Answers

  • Here are a couple of options in no specific order. Note with the amount of data you are working with even the best library and/or method is going to take time.

    Depending on if the work is being done on a local computer or server can limit your options.

    • Gembox spreadsheet library (paid for), here is one example. I own a copy and know it's fast.
    • A free library SpreadSheetLight. Easy to use.
    • OleDb data provider, most likely this will not do well with a large data set and has a column limit.
    • Export data to a .csv file. When Excel is installed it becomes the default program to open .csv files. Once opened the user can save the file as a native Excel file.
    • Aspose Cells (paid for library), works well from my experience but is not cheap (where Gembox is an acceptable price). 
    • Using Excel automation, you need to be extremely careful with object management else you can leak memory during the processing or after processing. There are many articles on the web on this. Excel automation is not well suited to run server side. There are several directions to go with here.
    • Using OpenXML (which libraries such as SpreadSheetLight use under the covers) but going this direction needs you to understand the underlying model of Open XML.
    • If you know how to code in VB.NET see the following, the code used can be written in a VB.NET class project and called from C#. 

    My two top picks are SpreadSheetLight and GemBox. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, December 27, 2017 10:42 AM
    Moderator
  • I believe that the native format for Office is now zip files with XML data. See the following:

    The following is the code from the second article listed above:

     
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System;
    using System.Linq;
    
    namespace CreateWorkbook
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(
                    System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WorkbookSample.xlsx"),
                    SpreadsheetDocumentType.Workbook))
                {
                    // create the workbook
                    spreadSheet.AddWorkbookPart();
                    spreadSheet.WorkbookPart.Workbook = new Workbook();     // create the worksheet
                    spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
    
                    // create sheet data
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(new SheetData());
                    // create row
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
                    // create cell with data
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(
                          new Cell() { CellValue = new CellValue("101") });
    
                    // save worksheet
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();
                    // create the worksheet to workbook relation
                    spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
                    spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
                    {
                        Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
                        SheetId = 1,
                        Name = "test"
                    });
                    spreadSheet.WorkbookPart.Workbook.Save();
                }
            }
        }
    }
    

    You will need to add a reference to DocumentFormat.OpenXml for that.



    Sam Hobbs
    SimpleSamples.Info

    Wednesday, December 27, 2017 7:56 PM

All replies

  • Here are a couple of options in no specific order. Note with the amount of data you are working with even the best library and/or method is going to take time.

    Depending on if the work is being done on a local computer or server can limit your options.

    • Gembox spreadsheet library (paid for), here is one example. I own a copy and know it's fast.
    • A free library SpreadSheetLight. Easy to use.
    • OleDb data provider, most likely this will not do well with a large data set and has a column limit.
    • Export data to a .csv file. When Excel is installed it becomes the default program to open .csv files. Once opened the user can save the file as a native Excel file.
    • Aspose Cells (paid for library), works well from my experience but is not cheap (where Gembox is an acceptable price). 
    • Using Excel automation, you need to be extremely careful with object management else you can leak memory during the processing or after processing. There are many articles on the web on this. Excel automation is not well suited to run server side. There are several directions to go with here.
    • Using OpenXML (which libraries such as SpreadSheetLight use under the covers) but going this direction needs you to understand the underlying model of Open XML.
    • If you know how to code in VB.NET see the following, the code used can be written in a VB.NET class project and called from C#. 

    My two top picks are SpreadSheetLight and GemBox. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, December 27, 2017 10:42 AM
    Moderator
  • I believe that the native format for Office is now zip files with XML data. See the following:

    The following is the code from the second article listed above:

     
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System;
    using System.Linq;
    
    namespace CreateWorkbook
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(
                    System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WorkbookSample.xlsx"),
                    SpreadsheetDocumentType.Workbook))
                {
                    // create the workbook
                    spreadSheet.AddWorkbookPart();
                    spreadSheet.WorkbookPart.Workbook = new Workbook();     // create the worksheet
                    spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
    
                    // create sheet data
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(new SheetData());
                    // create row
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
                    // create cell with data
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(
                          new Cell() { CellValue = new CellValue("101") });
    
                    // save worksheet
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();
                    // create the worksheet to workbook relation
                    spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
                    spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
                    {
                        Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
                        SheetId = 1,
                        Name = "test"
                    });
                    spreadSheet.WorkbookPart.Workbook.Save();
                }
            }
        }
    }
    

    You will need to add a reference to DocumentFormat.OpenXml for that.



    Sam Hobbs
    SimpleSamples.Info

    Wednesday, December 27, 2017 7:56 PM
  • Hello AKSHAY,

    And there is a example that will avoid some issues when you export large data to Excel files.

    https://www.codeproject.com/Tips/659666/Export-very-large-data-to-Excel-file

    Best regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 28, 2017 9:31 AM
    Moderator
  • See what I can achieve with Spire.XLS based on your input. Hope you find it helpful.

    Workbook wb = new Workbook();
    Worksheet sheet = wb.Worksheets[0];
    List<object> list = new List<object>();
    object[] array = list.ToArray();
    //write array into worksheet from the specified column and row
    sheet.InsertArray(object[] arrObject, int firstRow, int firstColumn, bool isVertical);



    • Edited by Ezreal93 Tuesday, January 9, 2018 8:30 AM
    Tuesday, January 9, 2018 8:18 AM
  • I've experimented with EPPlus.  It uses OpenXML.  It provides an easy method to generate Excel workbooks.  I was using it to generate an Excel workbook on a webserver.  There is a version for .NET Core 2.0.  I was running on Linux. You can easily create graphs and format data.  I didn't test moving a large amount of data but it was very fast on about 1000 rows.  I didn't end up using it since the client wanted a pdf of the key graphs and pivot tables.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using OfficeOpenXml;
    using OfficeOpenXml.Drawing;
    using System.Drawing;
    
    namespace EPPlusInAction
    {
    	class Sample1
    	{
    		// This is the Second Release of the Article:
    		// http://www.codeproject.com/Articles/680421/Create-Read-Edit-Advance-Excel-2007-2010-Report-in
    		// Sample 1 shows you the following:
    		/*
    		 * 1. Creating a New Workbook
    		 * 2. Creating a New Worksheet
    		 * 3. Accessing Cells by Row Index and Column Index
    		 * 4. Accessing Cells by its Address
    		 * 5. Inserting Values in Cells
    		 * 6. Setting Relative Formula Reference
    		 * 7. Adding Excel Function SUBTOTAL()
    		 * 8. Formatting the Style of a Range of Cells
    		 * 9. Setting Number Format for a Range of Cells
    		 * 10. Enabling Filter Feature of MS Excel
    		 * 11. Enabling 'AutoFit' of Cells
    		 * 12. Setting Header and Footer of the Worksheet
    		 * 13. Setting Printer Properties
    		 * 14. Setting Page Layout
    		 * 15. Setting Custom Property
    		 */
    		// You will find the published article about above topics here:
    		// http://www.codeproject.com/Tips/681412/Insert-Access-Format-Filter-Setting-Formula-Header
    		public static string RunSample1(DirectoryInfo outputDir)
    		{
                string outPath = Path.Combine(outputDir.FullName, "Sample1.xlsx");
    			FileInfo newFile = new FileInfo(outPath);
    			// If any file exists in this directory having name 'Sample1.xlsx', then delete it
    			if (newFile.Exists)
    			{
    				newFile.Delete(); // ensures we create a new workbook
    				newFile = new FileInfo(outPath);
    			}
    			using (ExcelPackage package = new ExcelPackage(newFile))
    			{
    				// Add a worksheet to the empty workbook
    				ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventry");
                    //var shape = worksheet.Drawings.AddShape("txtDesc", eShapeStyle.Line);
                    //shape.SetSize(100, 0);
                    //shape.SetPosition(10, 200);
                    //shape.Fill.Style = eFillStyle.SolidFill;
                    //shape.Fill.Color = Color.DarkSlateGray;
                    //shape.Border.LineStyle = OfficeOpenXml.Drawing.eLineStyle.Solid;
                    //shape.Border.Width = 5;
                    //shape.Border.Fill.Color = Color.Black;
                    // Add the headers
                    // Note: Accessing cells by row index and column index
                    worksheet.Cells[1, 1].Value = "ID";
    				worksheet.Cells[1, 2].Value = "Product";
    				worksheet.Cells[1, 3].Value = "Quantity";
    				worksheet.Cells[1, 4].Value = "Price";
    				worksheet.Cells[1, 5].Value = "Value";
    
    				// Add some items...
    				// Inserting values in the first row for: ID, Product, Quantity & Price respectively
    				// Note: Accessing cells by its address
    				worksheet.Cells["A2"].Value = 12001;
    				worksheet.Cells["B2"].Value = "Nails";
    				worksheet.Cells["C2"].Value = 37;
    				worksheet.Cells["D2"].Value = 3.99;
    
    				// Inserting values in the second row for the same...
    				worksheet.Cells["A3"].Value = 12002;
    				worksheet.Cells["B3"].Value = "Hammer";
    				worksheet.Cells["C3"].Value = 5;
    				worksheet.Cells["D3"].Value = 12.10;
    
    				// Inserting values in the third row for the same...
    				worksheet.Cells["A4"].Value = 12003;
    				worksheet.Cells["B4"].Value = "Saw";
    				worksheet.Cells["C4"].Value = 12;
    				worksheet.Cells["D4"].Value = 15.37;
    
    				// Note: we didn't do anything with the column 'Value'
    				// Actually, the value of the column 'Value' will be the product of 'Qauntity' and 'Price'
    				// So, we need to add a formula in the cells under column 'Value'
    				// Adding formula to the column 'Value'
    				worksheet.Cells["E2:E4"].Formula = "C2*D2";
    				// Note: We take the advantage of the one feature of MS Excel i.e. Relative Formula Reference
    				// For further reading about RELATIVE REFERENCE, click the link below:
    				// http://office.microsoft.com/en-in/excel-help/switch-between-relative-absolute-and-mixed-references-HP010342940.aspx
    
    				// Formatting style of the header
    				using (var range = worksheet.Cells[1, 1, 1, 5])
    				{
    					// Setting bold font
    					range.Style.Font.Bold = true;
    					// Setting fill type solid
    					range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
    					// Setting background color dark blue
    					range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
    					// Setting font color
    					range.Style.Font.Color.SetColor(Color.White);
    				}
    
    				// Formatting the footer row
    				// Setting top border of the footer row
    				worksheet.Cells["A5:E5"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
    				// Setting font bold of the footer row
    				worksheet.Cells["A5:E5"].Style.Font.Bold = true;
    
    				// Now, we want to show the Sub total in the footer row for the 'Quantity', 'Price' and 'Value' column
    				// Seeting formula for the footer row...
    				worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9, {0})", new ExcelAddress(2, 3, 4, 3).Address);
    				// Note: SUBTOTAL() is a Excel Function. If you don't know about this function, read this:
    				// http://office.microsoft.com/en-in/excel-help/subtotal-function-HP010062463.aspx
    
    				 //Now we need to format the values, as the values here, some are string, some are double, some are int
    				 //Setting Number Format...
    				 //Setting integer format for the column 'Quantity' and Setting decimal format for the column 'Price' and 'Value'
    				worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";	// Setting integer format
    				worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";	// Setting decimal format
    				// Here number format is the excel number format, if you don't know, please click and read:
    				// http://office.microsoft.com/en-in/excel-help/create-a-custom-number-format-HP010342372.aspx
    
    				// Now we enabling filter features of Excel in the cells
    				// If you don't know Excel Filtering, please click and read:
    				// http://office.microsoft.com/en-001/excel-help/filter-data-in-a-range-or-table-HP010073941.aspx
    				// Creating an Auto Filter for the range
    				worksheet.Cells["A1:E4"].AutoFilter = true;
    				// Setting text format for the column 'Product', as it will helps you during filtering
    				worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@";
    
    				// Setting AutoFit for all cells
    				worksheet.Cells.AutoFitColumns(0);
    
    				// Lets set the header text
    				worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventry";
    
    				// Add the page number to the right of the footer + total number of pages
    				worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
    
    				// Add the sheet name to center of the footer
    				worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
    
    				// Add the filepath to the left of the footer
    				worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
    
    				// At the time of printing, when page page breaks, then the header will come in the next page by enabling this settings...
    				worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:1"];
    				worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:E"];
    
    				// Change the sheet view to show it in page layout mode
    				worksheet.View.PageLayoutView = true;
    
    				// Setting some document properties
    				package.Workbook.Properties.Title = "Invertory";
    				package.Workbook.Properties.Author = "Debopam Pal";
    				package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 workbook using EPPlus";
    
    				// set some extended property values
    				package.Workbook.Properties.Company = "AdventureWorks Inc.";
    
    				// set some custom property values
    				package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
    				package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
    				// save our new workbook and we are done!
    				package.Save();
    			}
    
    			return newFile.FullName;
    		}
    	}
    }


    • Edited by mogulman52 Tuesday, January 9, 2018 4:59 PM
    Tuesday, January 9, 2018 4:57 PM