C # Implement a generic class complete instance _c# tutorial for converting data within a DataSet into Excel and Word files

The example in this article describes the C # implementation of the DataSet data into Excel and Word files in the general class. Share to everyone for your reference, specific as follows:

Not long ago because the project needs to write a C # to convert the dataset data into the common class of Excel and Word files, these about Excel, Word export methods, basically can achieve day-to-day needs, some of these methods can be exported to generate XML format data, and then import the database! Some of the shielding content has not been removed, to retain the convenience of learning reference. Finally, refer to the corresponding COM component of Office and export a method of the Excel object to invoke some of these methods and properties.

Using System;
Using System.Collections;
Using System.ComponentModel;
Using System.Data;
Using System.Web;
Using System.Web.SessionState;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Web.UI.HtmlControls;
Using System.IO;
Using System.Windows.Forms;
Using Microsoft.Office.Interop.Excel;
Using Microsoft.Office.Interop.Word;
Using Microsoft.Office.Core;
Using Owc=microsoft.office.interop;
Using System.Reflection;
Using System.Text;
 Summary description of namespace CaOnLine.ZWDB.DFObject {///<summary>///exportfiles. Role: The dataset DataSet data into Excel, Word file///Description: These on the Excel, Word export methods, basic can achieve day-to-day needs, some of these methods can be exported after the data///generate XML format, and then import the database!
 Some of the shielding content has not been removed, to retain the convenience of learning reference.
 Note: Refer to the corresponding COM component of Office and export a method of the Excel object to invoke some of these methods and properties. </summary> public class Exportfiles {///<summary>//////</summary>///#region//constructor PU Blic exportfiles () {///TODO: Add constructor logic//} #endregion///<summary>///invoke Excel.dll export Excel file/// </summary>///<param name= "ds" ></param>///#region//Call Excel.dll export Excel file///<summary>//////</su mmary>///<param name= "DS" >dataset data Xiu </param>///<param name= "Duser" > Logged-in users (such as session["Usern") Ame "]. Tostring ()) can be null or empty </param>///<param name= "TitleName" > Add a report title </param>///<param name= "f Ilepath > Specify the location of the file on the server (such as: Server.MapPath (".")
     can be null or empty </param>//////Why set a filepath here? The reason for this is as follows: FilePath receives the content is Server.MapPath (".")  The value of this parameter, which has an error in the reference to the///System.Web.HttpServerUtility hserver=new System.Web.HttpServerUtility () in this class because the page class is not inherited///
 So can only be called by the form of value, you have a good way to modify, that would be better! public void Datasettoexcel (DataSet ds,string duser,string titlename,string filepath) {//MICROSOFT.OFFICE.INTEROP.OWC1
  1 () appowc=new Microsoft.Office.Interop.Owc11 (); OWC.
  Owc11.spreadsheetclass xlsheet=new Microsoft.Office.Interop.Owc11.SpreadsheetClass (); #region//shielding content///originally intended to be implemented in this way, you must set the relevant permissions in IIS
  So give up, the code set to screen content, for learning reference!
Microsoft.Office.Interop.Excel.Application app=new Microsoft.Office.Interop.Excel.Application (); if (app==null)//{//throw new Exception ("System call Error (Excel.dll)");//}//App.
APPLICATION.WORKBOOKS.ADD (TRUE);
Workbookclass obook=new Workbookclass ();
Worksheetclass osheet=new Worksheetclass (); //Defines a Table object and a row object, and initializes its value with a DataSet//System.Data.DataTable Dt=ds.
Tables[0]; Osheet.get_range (app. Cells[1,1],app. CELLS[10,15]). HORIZONTALALIGNMENT=OWC.
Owc11.XlHAlign.xlHAlignCenter; Datarow[] Myrow=dt.
Select ();
int i=0; int CL=DT.
Columns.count; Get data table column headers//for (i=0;i<cl;i++)//{//app. Cells[1,i+1]=dt. Columns[i].
Caption.tostring (); App. Cells.addcomment (dt. Columns[i].
Caption.tostring ()); OSheet.Cells.AddComment (dt. Columns[i].
Caption.tostring ()); App. Cells=dt. Columns[i].
Caption.tostring (); Osheet.get_range (app. Cells,app. Cells). horizontalalignment=////app. Cells.addcomment=dt. Columns[i].
ToString (); Osheet.get_range (app. Cells,app. Cells). horizontalalignment=//} #endregion//Define table objects and row objects, and initialize their values with a dataset System.Data.DataTable Dt=ds.
  Tables[0]; Datarow[] Myrow=dt.
      Select ();
  int i=0;
  int col=1;
  int colday=col+1;
  int colsecond=colday+1;
  int colnumber=colsecond+1; int CL=DT.
  Columns.count;
  String Userfile=null; Merge cell Xlsheet.get_range (xlsheet. Cells[col,col],xlsheet.
  CELLS[COL,CL]). Set_mergecells (True); Add the title name if (titlename== "| | titlename==null) xlsheet.
  activesheet.cells[col,col]= "Add title (Advanced report)"; Else Xlsheet. Activesheet.cells[col,col]=titlename.
      Trim (); Determine if the pass-value user is an empty if (duser== "")
  Duser==null) userfile= "Dfsoft";
  else Userfile=duser; Sets the caption size Xlsheet.get_range (xlsheet. Cells[col,col],xlsheet. CELLS[COL,CL]).
  Font.set_size (13); Bold Heading Xlsheet.get_range (xlsheet. Cells[col,col],xlsheet. CELLS[COL,CL]).
   Font.set_bold (TRUE); Xlsheet.get_range (Xlsheet. Cells[colsecond,col],xlsheet. Cells[colsecond,cL]).
  Font.set_bold (TRUE); Sets the caption horizontally centered Xlsheet.get_range (xlsheet. Cells,xlsheet. Cells). Set_horizontalalignment (OWC.
  Owc11.XlHAlign.xlHAlignCenter); Sets the cell width//xlsheet.get_range (xlsheet. Cells,xlsheet.
  Cells). Set_columnwidth (9); Xlsheet.get_range (Xlsheet. Cells[colday,col],xlsheet.
  CELLS[COLDAY,CL]). Set_mergecells (True); Xlsheet. activesheet.cells[colday,col]= "Date:" +datetime.now.year.tostring () + "Year" +datetime.now.month.tostring () + "month" +
  DateTime.Now.Day.ToString () + "Day"; Xlsheet.get_range (Xlsheet. Cells[colday,col],xlsheet. CELLS[COLDAY,CL]). Set_horizontalalignment (OWC.
  Owc11.XlHAlign.xlHAlignRight); Gets the column headings for the datasheet, with \ t split between the headings, and the last column heading with the return character for (i=0;i<cl;i++) {xlsheet. Activesheet.cells[colsecond,i+1]=dt. Columns[i].
  Caption.tostring (); //Line-by-row processing data foreach (DataRow row in myrow) {//Current data write for (i=0;i<cl;i++) {xlsheet. Activesheet.cells[colnumber,i+1]=row[i]. ToString ().
  Trim ();
  } colnumber++; //Set Border line Xlsheet.get_range (xlsheet. Cells[colsecond,col],xlsheet. CelLS[COLNUMBER-1,CL]). Borders.set_linestyle (OWC.
  Owc11.XlLineStyle.xlContinuous); try {//xlsheet.get_range (xlsheet). Cells[2,1],xlsheet.
  CELLS[8,15]). Set_numberformat ("￥#,# #0.00");
  System.Web.HttpServerUtility hserver=new System.Web.HttpServerUtility (); Hserver.mappath (".")
  + "//testowc.xls"; Xlsheet. Export (filepath+ "//exportfiles//~$" +userfile+ ". xls", OWC. OWC11.SHEETEXPORTACTIONENUM.SSEXPORTACTIONNONE,OWC.
  Owc11.SheetExportFormat.ssExportXMLSpreadsheet); catch (Exception e) {throw new Exception ("System call error or open Excel file!")
  "+e);
  //web page definition HttpResponse resp;
  Resp=httpcontext.current.response; Resp.
  Contentencoding=system.text.encoding.getencoding ("GB2312"); Resp.
  Appendheader ("Content-disposition", "Attachment;filename=" +userfile+ ". xls"); Resp.
  Contenttype= "Application/ms-excel";
  String path=filepath+ "//exportfiles//~$" +userfile+ ". xls";
  System.IO.FileInfo file = new FileInfo (path); Resp.
  Clear (); Resp. AddHeader ("Content-length", file.
  Length.tostring ()); ResP.writefile (file.
  FullName); Resp.
 End (); #endregion///<summary>///export Excel file Classes///</summary>///<param name= "ds" ></param>///& Lt;param name= "FileName" ></param>///#region//Export Excel file class public void Datasettoexcel (DataSet ds,string Filena
  Me) {try {//web page definition//system.web.ui.page mypage=new System.Web.UI.Page ();
  HttpResponse resp;
  Resp=httpcontext.current.response; Resp.
  Contentencoding=system.text.encoding.getencoding ("GB2312"); Resp.
  Appendheader ("Content-disposition", "Attachment;filename=" +filename+ ". xls"); Resp.
  Contenttype= "Application/ms-excel";
  Variable definition string colheaders=null;
  String Is_item=null;
  Display format definition//////////////////File Flow operation definition//FileStream fs=new FileStream (filename,filemode.create,fileaccess.write);
  StreamWriter Sw=new StreamWriter (fs,system.text.encoding.getencoding ("GB2312"));
  StringWriter sfw=new StringWriter (); Defines a Table object and a row object, and initializes its value with a dataset System.Data.DataTable Dt=ds.
  Tables[0]; DaTarow[] Myrow=dt.
  Select ();
  int i=0; int CL=DT.
  Columns.count; Gets the column headings for the datasheet, with \ t split between headings, and the last column heading with carriage return for (i=0;i<cl;i++) {//if (i== (CL-1))//last column, plus \//COLHEADERS+=DT. Columns[i].
   Caption.tostring (); Else Colheaders+=dt. Columns[i].
  Caption.tostring () + "T"; } SFW.
  WriteLine (colheaders); Sw.
  WriteLine (colheaders); Line-by-row processing data foreach (DataRow row in myrow) {//Current data write for (i=0;i<cl;i++) {//if (i== (CL-1))//Is_item+=r Ow[i].
   ToString () + "\ n"; else Is_item+=row[i].
   ToString () + "\ T"; } SFW.
   WriteLine (Is_item); Sw.
   WriteLine (Is_item);
  Is_item=null; } resp.
  Write (SFW); Resp.
  Clear (); Resp.
  End ();
  catch (Exception e) {throw e; #endregion///<summary>///DataSet conversion, which translates the dataset into an Excel object///</summary>///<param name= "DS" >&lt ;/param>///<param name= "FileName" ></param>///<param name= "TitleName" ></param>///#regi On//using HTML+CSS to generate Excel public void DataseTtoexcel (DataSet ds,string filename,string titlename) {String exportfilename=null; if (Filename==null | |
  filename== "") exportfilename= "Dfsoft";
  else Exportfilename=filename;
  if (titlename== "| | | titlename==null) titlename=" Add the title (Advanced report); Defines a Table object and a row object, and initializes its value with a dataset System.Data.DataTable Dt=ds.
  Tables[0]; Datarow[] Myrow=dt.
  Select ();
  int i=0; int CL=DT.
  Columns.count;
  Web page definition HttpResponse resp;
  Resp=httpcontext.current.response; Resp.
  Clear (); Resp.
  Contentencoding=system.text.encoding.getencoding ("Utf-8"); Resp.
  Appendheader ("Content-disposition", "Attachment;filename=" +exportfilename+ ". xls"); Resp.
  Contenttype= "Application/vnd.ms-excel"; String begintab= "<table border= ' 0 ' cellpadding= ' 0 ' cellspacing= ' 0 ' style= ' border-right: #000000 0.1pt solid; Border-top: #000000 0.1pt solid; '
  > ";
  String endtab= "</table>";
  String Fileio=null;
  String Mainio=null; String titletab= "<tr><td colspan= '" +cl+ "' style= ' font-size:30px; ' align= ' CenteR ' ><b> ' +titlename+ "</b></td></tr><tr><td colspan= '" +cl+ "' align= ' right ' style = ' font-size:15px; ' > "+datetime.now.year.tostring () +" Year "+datetime.now.month.tostring () +" month "+datetime.now.day.tostring () +" Day  
     </td></tr> ";
  String begintr= "<tr>";
  String endtr= "</tr>"; for (i=0;i<cl;i++) {fileio+= "<td style= ' border-left: #000000 0.1pt solid; Border-bottom: #000000 1.0pt solid; font -size:15px ' align= ' center ' ><b> ' +dt. Columns[i].
  Caption.tostring () + "</b></td>";
  } fileio=begintr.tostring () +fileio.tostring () +endtr.tostring ();
  Line-by-row processing data foreach (DataRow row in myrow) {string outio=null; Current data write for (i=0;i<cl;i++) {outio+= "<td style= ' border-left: #000000 0.1pt solid; Border-bottom: #000000 1.0pt Solid font-size:15px ' align= ' center ' > ' +row[i].
  ToString () + "</td>";
  } mainio+=begintr.tostring () +outio.tostring () +endtr.tostring (); } fileio= "<center><table> "+titletab.tostring () +" <tr> "+begintab.tostring () +fileio.tostring () +mainio.tostring () +
  Endtab.tostring () + "</tr></table></center>"; Resp.
  Write (Fileio.tostring ()); Resp.
 End (); #endregion///<summary>///export Word file class///</summary>///<param name= "ds" ></param>///&L 
 T;param name= "filename" ></param>///#region//export Word file class public void Datasettoword (DataSet ds,string FileName)
  {try {//web page definition//system.web.ui.page mypage=new System.Web.UI.Page ();
  HttpResponse resp;
  Resp=httpcontext.current.response; Resp.
  Clear (); Resp.
  Buffer=true; Resp.
  Charset= "Utf-8"; Resp.
  Contentencoding=system.text.encoding.getencoding ("Utf-8"); Resp.
  Appendheader ("Content-disposition", "Attachment;filename=" +filename+ ". Doc"); Resp.
  Contenttype= "Application/ms-word";
  Variable definition string colheaders=null;
  String Is_item=null; Display format definition//////////////////File Flow operation definition//FileStream fs=new FileStream (filename,filemode.cReate,fileaccess.write);
  StreamWriter Sw=new StreamWriter (fs,system.text.encoding.getencoding ("GB2312"));
  StringWriter sfw=new StringWriter (); Defines a Table object and a row object, and initializes its value with a dataset System.Data.DataTable Dt=ds.
  Tables[0]; Datarow[] Myrow=dt.
  Select ();
  int i=0; int CL=DT.
  Columns.count; Gets the column headings for the datasheet, with \ t split between headings, and the last column heading with carriage return for (i=0;i<cl;i++) {//if (i== (CL-1))//last column, plus \//COLHEADERS+=DT. Columns[i].
   Caption.tostring (); Else Colheaders+=dt. Columns[i].
  Caption.tostring () + "T"; } SFW.
  WriteLine (colheaders); Sw.
  WriteLine (colheaders); Line-by-row processing data foreach (DataRow row in myrow) {//Current data write for (i=0;i<cl;i++) {//if (i== (CL-1))//Is_item+=r Ow[i].
   ToString () + "\ n"; else Is_item+=row[i].
   ToString () + "\ T"; } SFW.
   WriteLine (Is_item); Sw.
   WriteLine (Is_item);
  Is_item=null; } resp.
  Write (SFW); Resp.
  Clear (); Resp.
  End ();
  catch (Exception e) {throw e; #endregion///<summary>///DataSet conversion, which converts a dataset to a Word object
 </summary>///<param name= "ds" ></param>///<param name= "TitleName" ></param>/// #region//Run HTML+CSS generate Word file public void Datasettoword (DataSet ds,string filename,string titlename) {//Call office/// Note: Too slow discard apply this method//OWC. Word.Application oword=new OWC.
  Word.applicationclass (); OWC. Word._document odoc=new OWC.
  Word.documentclass ();
  String Exportfilename=null; if (Filename==null | |
  filename== "") exportfilename= "Dfsoft";
  else Exportfilename=filename;
  if (titlename== "| | | titlename==null) titlename=" Add the title (Advanced report); Defines a Table object and a row object, and initializes its value with a dataset System.Data.DataTable Dt=ds.
  Tables[0]; Datarow[] Myrow=dt.
  Select ();
  int i=0; int CL=DT.
  Columns.count; #region//String filetitle= <center><table><tr><td><b> report Test </b></td>
</tr></table> "+" \ n ";
String endfile= "</center>";
  Web page definition HttpResponse resp;
  Resp=httpcontext.current.response; Resp.
  Clear (); Resp. ConTentencoding=system.text.encoding.getencoding ("Utf-8"); Resp.
  Appendheader ("Content-disposition", "Attachment;filename=" +exportfilename+ ". Doc"); Resp.
Contenttype= "Application/vnd.ms-word";
System.IO.StringWriter osw=new StringWriter ();
System.Web.UI.HtmlTextWriter ohw=new System.Web.UI.HtmlTextWriter (OSW);
System.Web.UI.WebControls.DataGrid odg=new System.Web.UI.WebControls.DataGrid (); Odg.datasource=ds.
Tables[0];
Odg.databind ();
Odg.rendercontrol (OHW); Resp.
Write (Filetitle.tostring () +osw.tostring () +endfile.tostring ()); Resp.
  End (); #endregion string begintab= "<table border= ' 0 ' cellpadding= ' 0 ' cellspacing= ' 0 ' style= ' border-right: #000000 0.1pt Solid;border-top: #000000 0.1pt solid; '
  > ";
  String endtab= "</table>";
  String Fileio=null;
  String Mainio=null; String titletab= "<tr><td style= ' font-size:13px; ' align= ' center ' ><b> ' +titlename+ ' </b></ TD&GT;&LT;/TR&GT;&LT;TR&GT;&LT;TD align= ' right ' style= ' font-size:15px; ' > "+datetime.now.year.tostring () +" Year "+datetime.now.month.tostring () +" month "+datetime.now.day.tostring () +" Day  
     </td></tr> ";
  String begintr= "<tr>";
  String endtr= "</tr>"; for (i=0;i<cl;i++) {fileio+= "<td style= ' border-left: #000000 0.1pt solid; Border-bottom: #000000 1.0pt solid; font -size:15px ' align= ' center ' ><b> ' +dt. Columns[i].
  Caption.tostring () + "</b></td>";
  } fileio=begintr.tostring () +fileio.tostring () +endtr.tostring ();
  Line-by-row processing data foreach (DataRow row in myrow) {string outio=null; Current data write for (i=0;i<cl;i++) {outio+= "<td style= ' border-left: #000000 0.1pt solid; Border-bottom: #000000 1.0pt Solid font-size:15px ' align= ' center ' > ' +row[i].
  ToString () + "</td>";
  } mainio+=begintr.tostring () +outio.tostring () +endtr.tostring (); } fileio= "<center><table>" +titletab.tostring () + "<tr>" +begintab.tostring () +fileio.tostring () + Mainio.tostring () +endtab.tostring () + &LT;/tr></table></center> "; Resp.
  Write (Fileio.tostring ()); Resp.
 End ();

 } #endregion}}

Read more about C # Interested readers can view the site topics: "C # Operations Excel Skills Summary", "C # XML file Operation Tips Summary", "C # Common control usage Tutorial", "WinForm Control Usage Summary", "C # Data structure and algorithm tutorial", "C # An introductory course on object-oriented programming and a summary of thread usage tips for C # programming

I hope this article will help you with C # programming.