Export Excel documents through web programs

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.