Today, I received a task to export the data in the database to an Excel document. The system is a web program and uses the MVC Architecture. The first method is to save the. CSV file. First, read the data to a datatable, and then separate the data and write it into the file. Finally, download the file in the corresponding request.
The Code is as follows:
/// <Summary> /// export data /// </Summary> Public actionresult exportdata (INT processid) {var myprocessid = processid; var items = dB. necessarymaterialsdbset. include (P => P. processitem ). orderby (P => P. category ). tolist (). where (P => P. processitem. id = processid); If (! Items. any () {return redirecttoaction ("Index", new {processid = myprocessid});} var dt = new datatable (); DT. columns. add ("category"); DT. columns. add ("material name"); DT. columns. add ("material source"); DT. columns. add ("Source Type"); DT. columns. add ("original and photocopy"); DT. columns. add ("Remarks"); foreach (VAR materialse in items) {DT. rows. add (materialse. category, materialse. name, materialse. source, materialse. sourcetype, materialse. originalorcopy, materialse. note);} // generate the file string filename = items. first (). processitem. processname + "-mandatory materials .csv"; var Sw = new stringwriter (); var Title = new stringbuilder (); title. append (DT. columns [0]. columnname); For (INT I = 1; I <DT. columns. count; I ++) {Title. appendformat (", {0}", DT. columns [I]. columnname);} SW. writeline (title); var content = new stringbuilder (); foreach (datarow row in DT. rows) {content. append (row [0]); For (INT I = 1; I <DT. columns. count; I ++) {content. appendformat (", {0}", row [I]);} SW. writeline (content); content. clear ();} SW. close (); response. addheader ("content-disposition", "attachment; filename =" + server. urlencode (filename); response. contenttype = "Vnd. ms-excel.numberformat: yyyy-mm-dd "; response. contentencoding = system. text. encoding. getencoding ("gb2312"); response. write (SW); response. end (); Return redirecttoaction ("Index", new {processid = myprocessid });}
But there is a problem. I need to export an Excel file with a merged cell style, which does not seem to work.
As shown in:
The first idea was to use the Office excel api. A colleague suggested using table to construct it. The results are good.
The constructor is constructed in HTML. Use the <Table> <tr> <TB> label to control rows, columns, and content.
You can also set attributes in the table, such as <Table border = '1'> and <TD width = '000000'> <br>.
The rowspan attribute is used for cross-row settings. For example, "<TD rowspan = 5> indicates cross-row (merge cells ).
There was such a good method, so the code was written in this way, debugging and generation, and the effect was very good. The figure above was generated using the table method.
I will paste some instance code below:
Expmaterialstoexcelmodel. CS (this is used to store the exported data class structure)
Public class expmaterialstoexcelmodel {// <summary> /// process name /// </Summary> Public string name {Get; set ;} /// <summary> /// Approval Items /// </Summary> public list <Category> categorys {Get; set;} public expmaterialstoexcelmodel () {categorys = new list <Category> ();}} public class category {// <summary> // approval item name // </Summary> Public string name {Get; set ;} /// <summary> /// subject Department /// </Summary> Public String Department {Get; set ;} /// <summary> /// contact and phone number /// </Summary> Public String personandphone {Get; set ;} /// <summary >/// material /// </Summary> public list <materialsinfo> materialslist {Get; set;} public category () {materialslist = new list <materialsinfo> ();}} public class materialsinfo {// <summary> // rank order // </Summary> Public int sortnumb {Get; set ;} /// <summary> /// material name /// </Summary> Public string name {Get; set ;} /// <summary> /// source type /// </Summary> Public String sourcetype {Get; set ;} /// <summary> /// source /// </Summary> Public String source {Get; set ;} /// <summary> /// remarks /// </Summary> Public String note {Get; Set ;}}
Expmaterialstoexcelmodel. CS (reads data from the database)
Private expmaterialstoexcelmodel getexpdata (INT processid) {Code omitted ...}
Getexpstring (generate exported table text) highlights
private StringBuilder GetExpString(ExpMaterialsToExcelModel tableData) { int rowCount = tableData.Categorys.Sum(category => category.MaterialsList.Count); var str = new StringBuilder(); str.Append("<meta http-equiv=\"content-type\" content=\"application/excel;charset=utf-8\" />"); str.Append("<table border='1'>"); str.Append("<tr>"); str.AppendFormat("<td width='100' rowspan='{0}'>{1}</td>", rowCount, tableData.Name); for (int i = 0; i < tableData.Categorys.Count; i++) { if (i > 0) { str.Append("<tr>"); } var category = tableData.Categorys[i]; rowCount = category.MaterialsList.Count; str.AppendFormat("<td width='100' rowspan='{0}'>{1}</td>", rowCount, category.Name); str.AppendFormat("<td width='100'>{0}</td>", category.MaterialsList[0].SourceType); str.AppendFormat("<td width='200'>{0}.{1}</td>", category.MaterialsList[0].SortNumb, category.MaterialsList[0].Name); str.AppendFormat("<td width='100'>{0}</td>", category.MaterialsList[0].Note); str.AppendFormat("<td width='100' rowspan='{0}'>{1}</td>", rowCount, category.Department); str.AppendFormat("<td width='100' rowspan='{0}'>{1}</td>", rowCount, category.PersonAndPhone); str.Append("</tr>"); for (int j = 1; j < category.MaterialsList.Count; j++) { str.Append("<tr>"); str.AppendFormat("<td width='100'>{0}</td>", category.MaterialsList[j].SourceType); str.AppendFormat("<td width='200'>{0}.{1}</td>", category.MaterialsList[j].SortNumb, category.MaterialsList[j].Name); str.AppendFormat("<td width='100'>{0}</td>", category.MaterialsList[j].Note); str.Append("</tr>"); } } str.Append("</table>"); return str; }
Exportdata method (page request, return, and download files)
/// <Summary> /// export data /// </Summary> Public actionresult exportdata (INT processid) {var tabledata = getexpdata (processid ); vaR tablestring = getexpstring (tabledata); // output Excel String filename = tabledata. name + "material list"; response. clearcontent (); response. addheader ("content-disposition", String. format ("attachment; filename1_1_02.16.xls", httputility. urlencode (filename, encoding. utf8); response. contenttype = "application/Excel"; response. charset = "UTF-8"; response. write (tablestring); response. end (); var myprocessid = processid; return redirecttoaction ("Index", new {processid = myprocessid });}
This method saves the trouble of calling the Excel API, which is very convenient. It fully meets the needs of merging cells (merging rows and columns.