Whether you are working on the Web or writing winform programs, you are always exporting Excel data because of the scientific notation problem. If the character is too long (such as the ID card number) in the exported Excel file, the scientific notation of long strings is displayed, and repeated data import leads to errors.
The solution is to process each record string everywhere or when the storage is about to export. In Asp.net, I usually put the data to be exported in the gridview mesh, first, it processes the data in the string format when the grid is set, and then exports the data in excel in the normal form to solve the problem. My code is very simple: when you set the gridview control, format protected void gerror_rowdatabound (Object sender, gridviewroweventargs E) in the rowdatabound event squadron data)
{
// 1) Text: VND. ms-excel.numberformat: @ // 2) Date: VND. ms-excel.numberformat: yyyy/mm/DD // 3) number: VND. ms-excel.numberformat: #, #0.00 // 4) Currency: VND. ms-excel.numberformat: ￥ #, #0.00 // 5) percentage: VND. ms-excel.numberformat: # 0.00% for (INT I = 0; I <E. row. cells. count; I ++)
{
If (E. Row. rowtype = datacontrolrowtype. datarow)
E. Row. cells [I]. Attributes. Add ("style", "Vnd. ms-excel.numberformat :@");
}
} Then execute the operations everywhere and the problem will not occur. Protected void btnout_click (Object sender, eventargs E)
{
Response. Clear ();
Response. Buffer = true;
Response. charset = "gb2312 ";
Response. appendheader ("content-disposition", "attachment?filename=filename.xls ");
Response. contentencoding = system. Text. encoding. utf7; // set the output file type to an Excel file.
Response. contenttype = "application/MS-excel ";
System. Io. stringwriter ostringwriter = new system. Io. stringwriter ();
System. Web. UI. htmltextwriter ohtmltextwriter = new system. Web. UI. htmltextwriter (ostringwriter );
This. gerror. rendercontrol (ohtmltextwriter );
Response. Output. Write (ostringwriter. tostring ());
Response. Flush ();
Response. End ();} public override void verifyrenderinginserverform (Control)
{
// Base. verifyrenderinginserverform (control );
} During winform program development, the solution was to try to process the Excel Object format mysheet. cells. numberformat = "#" during the export process. Finally, we used a record-by-record method to convert the character format one by one, that is, adding "'". The main part of the code I wrote is as follows # region executes data export
Try
{
// Export to excel
Excel. applicationclass my = new excel. applicationclass ();
If (MY = NULL)
{
MessageBox. Show ("an Excel object cannot be created, or Excel is not installed in your system ");
Return;
}
My. Visible = false;
Excel. Workbook mybook = (Excel. workbook) My. workbooks. Add (1 );
(Excel. worksheet) mybook. worksheets [1]). Name = "sheet1 ";
Excel. worksheet mysheet = (Excel. worksheet) mybook. worksheets [1];
// Mysheet. cells. numberformat = "#";
// Export column name
For (Int J = 0; j <this. dgvshow. Columns. Count; j ++)
{
If (this. dgvshow. Columns [J]. Visible = true)
{
Mysheet. cells [1, J + 1] = "'" + convert. tostring (this. dgvshow. columns [J]. headertext); // Add "'" To prevent scientific notation
}
}
// Export data
For (INT I = 0; I <this. dgvshow. Rows. Count; I ++)
{
For (Int J = 0; j <this. dgvshow. Columns. Count; j ++)
{
Mysheet. cells [I + 2, J + 1] = "'" + convert. tostring (this. dgvshow. Rows [I]. cells [J]. value );
}
} If (savefilename! = "")
{
Try
{
// Mybook. Save ();
Mybook. savecopyas (savefilename );
MessageBox. Show ("Excel file exported successfully! ");
}
Catch (exception ex)
{
MessageBox. Show ("An error occurred while exporting the file. The file may be being opened! \ N "+ ex. Message );
}
} GC. Collect ();
}
Catch (exception ex)
{
MessageBox. Show ("an error occurred during data export. The following is a detailed error message: \ n" + ex. Message );
Return;
}
# Endregion
Each record is processed. If there is a large amount of data, it will affect the speed. There must be many better ways to learn and improve together.
