How to export a large amount of data to an Excel file

When I was preparing to write this article, I saw the group message sent by my colleagues. I am evil. I believe that all the people who read this article are pure. Let me share it with you! Main Content: due to changes in requirements, the company needs a server for transfer. The company has prepared an ordinary PC as the server, and then asked a colleague to check whether the server can be used. Colleague A replied: I don't know if this PC can survive? MM replied: "Well, which part of the device do you think cannot withstand ?" My colleague replied, "It should not be exposed, but it may affect the number of times of use if it cannot be sustained ." ~~ Then various associations. (If you see this, please click "recommendation + 1" below to eliminate guilt! )

Well, this is the end of the joke. Go to the topic. Recently, the company's projects involve millions of data export issues. Foreigners (customers) need to sort and export data, so specify the Excel format to export. We all know that the maximum number of data records supported by Excel files is 65535. How can we display millions of data records in the same Excel file? I browsed it on the Internet, but I finally heard that the office installation directory has an API, So I sorted it out. If you know the answer, please click "recommendation + 1" below and then you can turn off the browser. If you do not know the answer, I will not go around. Please confirm the following code, click "recommendation + 1 ". Thank you for providing the code:

BeginCode

/// <Summary> /// export large amounts of data /// </summary> /// <param name = "table"> </param> /// <param name = "saveFileName"> </param> public static void ExportToExcel (System. data. dataTable table, string saveFileName) {try {if (table = null) {return;} bool fileSaved = false; // ExcelApp xlApp = new ExcelApp (); application xlApp = new Application (); if (xlApp = null) {return;} Workbooks workbooks = xlApp. workbooks; Workbook workbook = workbooks. add (XlWBATemplate. xlWBATWorksheet); Worksheet worksheet = (Worksheet) workbook. worksheets [1]; // get sheet1 long rows = table. rows. count;/* the following two lines of code comment when the number of rows exceeds the row, an exception occurs: the exception is from HRESULT: 0x800A03EC. Because: Excel 2003 each sheet only supports the largest row of Data // Range fchR = worksheet. get_Range (worksheet. cells [1, 1], worksheet. cells [table. rows. count + 2, gridview. columns. view. visibleColumns. count + 1]); // fchR. value2 = datas; */if (rows> 65535) {long pageRows = 60000; // defines the number of lines displayed on each page. The number of lines must be less than int scount = (int) (rows/pageRows); if (scount * pageRows <table. rows. count) // when the total number of rows is not divisible by pageRows, the number of pages may not be allowed after rounding {scount = scount + 1;} for (int SC = 1; SC <= scount; SC ++) {if (SC> 1) {object missing = System. reflection. missing. value; worksheet = (Microsoft. office. interop. excel. worksheet) workbook. worksheets. add (missing, missing); // Add a sheet} else {worksheet = (Worksheet) workbook. worksheets [SC]; // get sheet1} string [,] datas = new string [pageRows + 1, table. columns. count + 1]; for (int I = 0; I <table. columns. count; I ++) // write the field {datas [0, I] = table. columns [I]. caption;} Range range = worksheet. get_Range (worksheet. cells [1, 1], worksheet. cells [1, table. columns. count]); range. interior. colorIndex = 15; // 15 indicates the gray range. font. bold = true; range. font. size = 9; int init = int. parse (SC-1) * pageRows ). toString (); int r = 0; int index = 0; int result; if (pageRows * SC> = table. rows. count) {result = table. rows. coun T;} else {result = int. parse (pageRows * SC ). toString () ;}for (r = init; r <result; r ++) {index = index + 1; for (int I = 0; I <table. columns. count; I ++) {if (table. columns [I]. dataType = typeof (string) | table. columns [I]. dataType = typeof (Decimal) | table. columns [I]. dataType = typeof (DateTime) | table. columns [I]. dataType = typeof (int) | table. columns [I]. dataType = typeof (double )) {Object obj = table. Rows [r] [table. Columns [I]. ColumnName]; datas [index, I] = obj = null? "": "'" + Obj. toString (). trim (); // In obj. toString () is enclosed in single quotes to Prevent automatic conversion format} Range fchR = worksheet. get_Range (worksheet. cells [1, 1], worksheet. cells [index + 2, table. columns. count + 1]); fchR. value2 = datas; worksheet. columns. entireColumn. autoFit (); // adaptive column width. Range = worksheet. get_Range (worksheet. cells [1, 1], worksheet. cells [index + 1, table. columns. count]); // 15 indicates the gray range. font. size = 9; range. rowHeight = 14.25; range. borders. lineStyle = 1; range. horizontalAlignment = 1 ;}} else {string [,] datas = new string [table. rows. count + 2, table. columns. count + 1]; for (int I = 0; I <table. columns. count; I ++) // write the field {datas [0, I] = table. columns [I]. caption ;} Range range = worksheet. get_Range (worksheet. cells [1, 1], worksheet. cells [1, table. columns. count]); range. interior. colorIndex = 15; // 15 indicates the gray range. font. bold = true; range. font. size = 9; int r = 0; for (r = 0; r <table. rows. count; r ++) {for (int I = 0; I <table. columns. count; I ++) {if (table. columns [I]. dataType = typeof (string) | table. columns [I]. dataType = typeof (Decimal) | table. columns [I]. DataType = typeof (DateTime) {object obj = table. rows [r] [table. columns [I]. columnName]; datas [r + 1, I] = obj = null? "": "'" + Obj. toString (). trim (); // In obj. toString () is enclosed in single quotes to Prevent automatic conversion format} // System. windows. forms. application. doEvents ();} Range fchR = worksheet. get_Range (worksheet. cells [1, 1], worksheet. cells [table. rows. count + 2, table. columns. count + 1]); fchR. value2 = datas; worksheet. columns. entireColumn. autoFit (); // adaptive column width. Range = worksheet. get_Range (worksheet. cells [1, 1], worksheet. cells [table. rows. count + 1, table. columns. count]); // 15 indicates the gray range. font. size = 9; range. rowHeight = 14.25; range. borders. lineStyle = 1; range. horizontalAlignment = 1;} if (saveFileName! = "") {Try {workbook. saved = true; workbook. saveCopyAs (saveFileName); fileSaved = true;} catch (Exception ex) {fileSaved = false ;}} else {fileSaved = false;} xlApp. quit (); GC. collect (); // force destroy // web Background box, winform can use messagebox .. system. web. httpContext. current. response. write ("<Script Language = JavaScript>... alert ('export Success! File path in D disk root directory. '); </Script> ");} catch (Exception) {System. web. httpContext. current. response. write ("<Script Language = JavaScript>... alert ('export Error! Please contact administrator! '); </Script> ");}}

EndCode

OK, that's all. Pass a DataTable and a path to save the file. The execution result is as follows: (the figure shows the testing effect of 30 million pieces of data on Office 2007, there is no metering execution time, but it just takes a sip of water and the time when the lid is covered ):)

 

Image 2:

In order not to be overwhelmed, I carefully dropped sensitive data A. I suddenly found that QQ functions were weak, and it was still refreshing in windows ~~

Okay, this is the place where there is a mistake. Please make a brick and look forward to opening your face. Thank you for coming ~~