Use the myxls class to export an Excel file

Use the myxls class to export an Excel file

Problems:
In the past, our company used a method of exporting Excel: using two functions, we directly output data streams with HTML tags on webpages into files, and then changed the file suffix to XLS, this method is easy to write code, and the speed is also good. However, to export the xls file, you must put a hidden DataGrid on the page and re-bind it to export the data. One problem with this export is that this file is not actually a real xls file. We can open this file with notepad, and we can see the familiar HTML Tag, therefore, this file cannot be fully compatible with all EXCEL formats. If I open this file in Excel, insert a sheet in this file, and enter some content in the sheet, save it. If it is not saved, the file will be saved as a webpage file. You can try it on your own. However, if you save the file as an xls file, the actual xls file is saved.
Another method is the one I wrote in the previous post. Lao Wang's method of Directly Writing CSV files is very good for the applicability of large data volumes, however, for formatted data, the format is messy.

Solution:
Use the myxls control to export data. This is an open-source Excel export control, which can be downloaded directly from the Internet. This usage method is also very simple. You can directly reference this control and write it one by one in the Code. The following is the sample code:
Org. in2bits. myxls. xlsdocument Doc = new org. in2bits. myxls. xlsdocument ();
Doc. filename = datetime. now. tostring (). replace ("-",""). replace (":",""). replace ("", "") + ". xls "; Excel file name
Dataview DV = function. sqlexcuter_todataview (hidsql. Value); // data to be exported
Org. in2bits. myxls. worksheet sheet = Doc. Workbook. worksheets. addnamed ("sheet1"); // worksheet name
Org. in2bits. myxls. Cells = sheet. cells;
Int colnum = maid. count;
For (INT I = 0; I <colnum; I ++)
{
Cells. addvaluecell (1, (I + 1), datagrid1.columns. Headertext); // the name of the first column to be exported is obtained from the DataGrid.
}
For (INT I = 0; I <DV. Count; I ++)
{
For (Int J = 0; j <colnum; j ++)
{
If (J! = 4) // here I am here because my fourth column is in the date format and I want to remove the time, so I will process this value when each row is in the fourth column.
{
Cells. addvaluecell (I + 2), (J + 1), DV[J]. tostring ());
}
Else
{
Cells. addvaluecell (I + 2), (J + 1), DV[J]. tostring (). Split ('') [0]);
}
}
}
Doc. Send (); // output the prepared Excel file to the client.
The export speed of this control is also acceptable, and the exported xls file is a real Excel file that supports all Excel functions. When we open it in notepad, we will find that all the content is encoded.
The attachment contains the DLL to be used. A total of four DLL files must be referenced before the above Code can be used.