C # to generate Excel-CodePudding

I made a date by querying the database of the form, and the query of data export generated an Excel documents, found in the testing process, query time is too long, export data response time is longer, I used to query a month, a conservative estimate to more than 10 minutes to complete, want to consult everybody a great god how to optimize the process accordingly, cutting time, after click the export button to add a progress bar,

CodePudding user response:

Use what technical writing code to export? We all use NPOI components, no need to install Excel, quick speed, 10000 records for a few seconds (about 15 field),

CodePudding user response:

. Using Microsoft Office. Interop. Excel

CodePudding user response:

reference 1st floor datafansbj response:

derived using what technical writing code? We all use NPOI components, no need to install Excel, quick speed, 10000 records for a few seconds (about 15 fields),

one hundred ten thousand records to 30 minutes

CodePudding user response:

Are you sure they aren't the SQL query has a problem???????

CodePudding user response:

Old problem, Microsoft Office. Interop. Excel not only speed is slow, but also requires each other's computer to install Office, the.net environment should use NPOI to export Excel, export of thousands of data before it only takes a few seconds to go

CodePudding user response:

reference 5 floor HerryDong reply:

old problem, Microsoft. Office. Interop. Excel not only speed is slow, but also requires each other's computer to install Office, the.net environment should use NPOI to export Excel, export of thousands of data before it only takes a few seconds to go

ok, when the data is little data to a big response is slow, the realization of the great god ever NPOI method source code? I want to refer to

CodePudding user response:

refer to 6th floor weixin_50199980 response:

Quote: refer to the 5 floor HerryDong response:
old problem, Microsoft. Office. Interop. Excel not only speed is slow, but also requires each other's computer to install Office, the.net environment should use NPOI to export Excel, export of thousands of data before it only takes a few seconds to go

ok, when the data is little data to a big response is slow, the realization of the great god ever NPOI method source code? I would like to refer to

Not right,

Lot - dotnetcore/NPOI: a..net library for reading and writing Microsoft Office binary and OOXML file formats.
https://github.com/dotnetcore/NPOI

 
Var newFile=@ "newbook. Core. XLSX"; 

Using (var fs=new FileStream (newFile, FileMode. Create, FileAccess. Write)) {

IWorkbook workbook=new XSSFWorkbook (); 

ISheet sheet1=workbook. CreateSheet (" sheet1 "); 

Sheet1. AddMergedRegion (new CellRangeAddress (0, 0, 0, 10)); 
Var rowIndex=0; 
IRow row=sheet1. CreateRow (rowIndex); 
Row. Height=30 * 80; 
Row. CreateCell (0). SetCellValue (" this is the content "); 
Sheet1. AutoSizeColumn (0); 
RowIndex++; 

Var sheet2=workbook. CreateSheet (" sheet2 "); 
Var style1=workbook. CreateCellStyle (); 
Style1. FillForegroundColor=HSSFColor. Blue. Index2; 
Style1. FillPattern=FillPattern. SolidForeground; 

Var style2=workbook. CreateCellStyle (); 
Style2. FillForegroundColor=HSSFColor. Yellow. Index2; 
Style2. FillPattern=FillPattern. SolidForeground; 

Var cell2=sheet2. CreateRow (0). CreateCell (0); 
Cell2. CellStyle=style1; 
Cell2. SetCellValue (0); 

Cell2=sheet2. CreateRow (1). CreateCell (0); 
Cell2. CellStyle=style2; 
Cell2. SetCellValue (1); 

Workbook. Write (fs); 
}