SoFunction
Updated on 2025-03-07

C# implements the complete example of the general class that converts data in DataSet into Excel and Word files

This article describes the common class that implements data in DataSet into Excel and Word files in C#. Share it for your reference, as follows:

Not long ago, because of the need for the project, I wrote a C# to convert the data in the DataSet into a general class that can improve the data in Excel and Word files. These export methods about Excel and Word can basically implement daily needs. Some of them can export the data and generate Xml format and then import it into the database! Some blocked content has not been removed, so it is preserved for learning and reference. Finally, please refer to the corresponding COM component of Office. To export an Excel object, you need to call some of the methods and properties.

using System;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using OWC=;
using ;
using ;
namespace 
{
 /// <summary>
 /// Summary description of ExportFiles. /// Function: convert data in the DataSet data set into Excel and Word files /// Description: These export methods about Excel and Word can basically implement daily needs, some of which can export data after /// Generate Xml format and import it into the database!  Some blocked content has not been removed, so it is preserved for learning and reference. /// Note: Please refer to the corresponding COM component of Office. To export an Excel object, some of the methods and properties must be called. /// </summary>
 public class ExportFiles
 {
 /// <summary>
 ///
 /// </summary>
 ///
 #region //Constructor public ExportFiles()
 {
  //
  // TODO: Add constructor logic here  //
 }
 #endregion
 /// <summary>
 /// Call to export Excel file /// </summary>
 /// <param name="ds"></param>
 ///
 #region // Call to export Excel file     /// <summary>
     ///
     /// </summary>
     /// <param name="ds">DataSet data machine</param>     /// <param name="Duser">Login user (such as session["username"].Tostring()) can be null or empty</param>     /// <param name="titlename">Add a report title</param>     /// <param name="filepath">Specify the storage address of the file on the server (such as: (".")) can be null or empty</param>     ///
     /// Why set a filepath here?     /// The reason is as follows: the content received by filepath is (".") parameter value, which is in this class for     /// There is an error in the reference of HServer=new () because the Page class is not inherited     /// So you can only call it in the form of passing values. If you have a good way to modify it, that would be the best! public void DataSetToExcel(DataSet ds,string Duser,string titlename,string filepath)
 {
  //.Owc11() appowc=new .Owc11();
  OWC. xlsheet=new .();
  #region //Block content ///I originally wanted to use the following method to implement it, but relevant permissions must be set in IIS  /// So I gave up and set the code to block content for learning reference!  ///
//   app=new ();
//
//  if(app==null)
//  {
// throw new Exception("System call error()");//  }
//  (true);
//  WorkbookClass oBook=new WorkbookClass();
//      WorksheetClass oSheet=new WorksheetClass();
//
// //Define table objects and row objects, and initialize their values ​​with DataSet at the same time//   dt=[0];
//   oSheet.get_Range([1,1],[10,15]).HorizontalAlignment=OWC.;
//  DataRow[] myRow=();
//  int i=0;
//  int cl=;
// // Obtain the titles of each column of the data table//  for(i=0;i&lt;cl;i++)
//  {
//  [1,i+1]=[i].();
//  //([i].());
//  //([i].());
//  //=[i].();
//  //oSheet.get_Range(,).HorizontalAlignment=
//  //=[i].ToString();
//  //oSheet.get_Range(,).HorizontalAlignment=
//  }
  #endregion
  //Define table objects and row objects, and use DataSet to initialize their values.       dt=[0];
  DataRow[] myRow=();
      int i=0;
  int col=1;
  int colday=col+1;
  int colsecond=colday+1;
  int colnumber=colsecond+1;
  int cl=;
  string userfile=null;
  //Merge cells  xlsheet.get_Range([col,col],[col,cl]).set_MergeCells(true);
  //Add a title name  if(titlename=="" || titlename==null)
    [col,col]="Add Title (Advanced Report)";
  else
  [col,col]=();
      //Judge whether the passed value user is empty  if(Duser=="" || Duser==null)
  userfile="DFSOFT";
  else
  userfile=Duser;
  //Set the title size  xlsheet.get_Range([col,col],[col,cl]).Font.set_Size(13);
  //Bold title  xlsheet.get_Range([col,col],[col,cl]).Font.set_Bold(true);
   xlsheet.get_Range([colsecond,col],[colsecond,cl]).Font.set_Bold(true);
  //Set the title level centered  xlsheet.get_Range(,).set_HorizontalAlignment(OWC.);
  //Set cell width  //xlsheet.get_Range(,).set_ColumnWidth(9);
  xlsheet.get_Range([colday,col],[colday,cl]).set_MergeCells(true);
  [colday,col]="date:"+()+"Year"+()+"moon"+()+"day ";
  xlsheet.get_Range([colday,col],[colday,cl]).set_HorizontalAlignment(OWC.);
  //Get the headers of each column in the data table, divide them with \t, add the carriage return after the last column title  for(i=0;i&lt;cl;i++)
  {
  [colsecond,i+1]=[i].();
  }
      // Process data line by line  foreach(DataRow row in myRow)
  {
  //Current data is written  for(i=0;i&lt;cl;i++)
  {
   [colnumber,i+1]=row[i].ToString().Trim();
  }
  colnumber++;
  }
  //Set border lines  xlsheet.get_Range([colsecond,col],[colnumber-1,cl]).Borders.set_LineStyle(OWC.);
  try
  {
  //xlsheet.get_Range([2,1],[8,15]).set_NumberFormat("￥#,##0.00");
  //  HServer=new ();
  //(".")+"//";
  (filepath+"//exportfiles//~$"+userfile+".xls",OWC.,OWC.);
  }
  catch(Exception e)
  {
  throw new Exception("System call error or an open Excel file!"+e);
  }
  //Web page definition  HttpResponse resp;
  resp=;
  =("GB2312");
  ("Content-disposition","attachment;filename="+userfile+".xls");
  ="application/ms-excel";
  string path=filepath+"//exportfiles//~$"+userfile+".xls";
   file = new FileInfo(path);
  ();
  ("content-length",());
  ();
  ();
 }
 #endregion
 /// &lt;summary&gt;
 /// Export Excel file class /// &lt;/summary&gt;
 /// &lt;param name="ds"&gt;&lt;/param&gt;
 /// &lt;param name="FileName"&gt;&lt;/param&gt;
 ///
 #region //Export Excel file class public void DataSetToExcel(DataSet ds,string FileName)
 {
  try
  {
  //Web page definition  // mypage=new ();
  HttpResponse resp;
  resp=;
  =("GB2312");
  ("Content-disposition","attachment;filename="+FileName+".xls");
  ="application/ms-excel";
  //Variable definition  string colHeaders=null;
  string Is_item=null;
  //Display format definition/////////////////////////////////  //File flow operation definition  // FileStream fs=new FileStream(FileName,,);
  //StreamWriter sw=new StreamWriter(fs,("GB2312"));
  StringWriter sfw=new StringWriter();
  //Define table objects and row objects, and use DataSet to initialize their values.   dt=[0];
  DataRow[] myRow=();
  int i=0;
  int cl=;
  //Get the headers of each column in the data table, divide them with \t, add the carriage return after the last column title  for(i=0;i&lt;cl;i++)
  {
   //if(i==(cl-1)) //The last column, add \n   // colHeaders+=[i].();
   //else
   colHeaders+=[i].()+"\t";
  }
  (colHeaders);
  //(colHeaders);
  // Process data line by line  foreach(DataRow row in myRow)
  {
   //Current data is written   for(i=0;i&lt;cl;i++)
   {
   //if(i==(cl-1))
   //  Is_item+=row[i].ToString()+"\n";
   //else
   Is_item+=row[i].ToString()+"\t";
   }
   (Is_item);
   //(Is_item);
   Is_item=null;
  }
  (sfw);
  //();
  ();
  }
  catch(Exception e)
  {
        throw e;
  }
 }
 #endregion
 /// &lt;summary&gt;
 /// Dataset conversion, that is, converting DataSet into Excel objects /// &lt;/summary&gt;
 /// &lt;param name="ds"&gt;&lt;/param&gt;
 /// &lt;param name="FileName"&gt;&lt;/param&gt;
 /// &lt;param name="titlename"&gt;&lt;/param&gt;
 ///
 #region //Use 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 Title (Advanced Report)";
  //Define table objects and row objects, and use DataSet to initialize their values.   dt=[0];
  DataRow[] myRow=();
  int i=0;
  int cl=;
  //Web page definition  HttpResponse resp;
  resp=;
  ();
  =("utf-8");
  ("Content-disposition","attachment;filename="+ExportFileName+".xls");
  ="application/-excel";
  string BeginTab="&lt;table border='0' cellpadding='0' cellspacing='0' style='border-right:#000000 0.1pt solid;border-top:#000000 0.1pt solid;'&gt;";
  string EndTab="&lt;/table&gt;";
  string FileIO=null;
  string MainIO=null;
  string TitleTab="&lt;tr&gt;&lt;td colspan='"+cl+"' style='font-size:30px;' align='center'&gt;&lt;b&gt;"+titlename+"&lt;/b&gt;&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td colspan='"+cl+"' align='right' style='font-size:15px;'&gt;"+()+"Year"+()+"moon"+()+"Sunday&nbsp;&nbsp;&nbsp;&nbsp;</td></tr>";
  string BeginTr="&lt;tr&gt;";
  string EndTr="&lt;/tr&gt;";
  for(i=0;i&lt;cl;i++)
  {
  FileIO+="&lt;td style='border-left:#000000 0.1pt solid; border-bottom:#000000 1.0pt solid; font-size:15px;' align='center'&gt;&lt;b&gt;"+[i].()+"&lt;/b&gt;&lt;/td&gt;";
  }
  FileIO=()+()+();
  // Process data line by line  foreach(DataRow row in myRow)
  {
  string OutIO=null;
  //Current data is written  for(i=0;i&lt;cl;i++)
  {
   OutIO+="&lt;td style='border-left:#000000 0.1pt solid; border-bottom:#000000 1.0pt solid; font-size:15px;' align='center'&gt;"+row[i].ToString()+"&lt;/td&gt;";
  }
  MainIO+=()+()+();
  }
  FileIO="&lt;center&gt;&lt;table&gt;"+()+"&lt;tr&gt;"+()+()+()+()+"&lt;/tr&gt;&lt;/table&gt;&lt;/center&gt;";
  (());
  ();
 }
 #endregion
 /// &lt;summary&gt;
 /// Export Word file class /// &lt;/summary&gt;
 /// &lt;param name="ds"&gt;&lt;/param&gt;
 /// &lt;param name="FileName"&gt;&lt;/param&gt;
 ///
 #region //Export Word file class public void DataSetToWord(DataSet ds,string FileName)
 {
  try
  {
  //Web page definition  // mypage=new ();
  HttpResponse resp;
  resp=;
  ();
  =true;
  ="utf-8";
  =("utf-8");
  ("Content-disposition","attachment;filename="+FileName+".doc");
  ="application/ms-word";
  //Variable definition  string colHeaders=null;
  string Is_item=null;
  //Display format definition/////////////////////////////////  //File flow operation definition  // FileStream fs=new FileStream(FileName,,);
  //StreamWriter sw=new StreamWriter(fs,("GB2312"));
  StringWriter sfw=new StringWriter();
  //Define table objects and row objects, and use DataSet to initialize their values.   dt=[0];
  DataRow[] myRow=();
  int i=0;
  int cl=;
  //Get the headers of each column in the data table, divide them with \t, add the carriage return after the last column title  for(i=0;i&lt;cl;i++)
  {
   //if(i==(cl-1)) //The last column, add \n   // colHeaders+=[i].();
   //else
   colHeaders+=[i].()+"\t";
  }
  (colHeaders);
  //(colHeaders);
  // Process data line by line  foreach(DataRow row in myRow)
  {
   //Current data is written   for(i=0;i&lt;cl;i++)
   {
   //if(i==(cl-1))
   //  Is_item+=row[i].ToString()+"\n";
   //else
   Is_item+=row[i].ToString()+"\t";
   }
   (Is_item);
   //(Is_item);
   Is_item=null;
  }
  (sfw);
  //();
  ();
  }
  catch(Exception e)
  {
  throw e;
  }
 }
 #endregion
 /// &lt;summary&gt;
 /// Dataset conversion, that is, converting DataSet into Word objects /// &lt;/summary&gt;
 /// &lt;param name="ds"&gt;&lt;/param&gt;
 /// &lt;param name="titlename"&gt;&lt;/param&gt;
 ///
 #region // Run html+css to generate Word files public void DataSetToWord(DataSet ds,string FileName,string titlename)
 {
  //Calling Office  //Note: The speed is too slow to give up applying this method  // oWord=new ();
  //._Document oDoc=new ();
  string ExportFileName=null;
  if(FileName==null || FileName=="")
  ExportFileName="DFSOFT";
  else
  ExportFileName=FileName;
  if(titlename=="" || titlename==null)
  titlename="Add Title (Advanced Report)";
  //Define table objects and row objects, and use DataSet to initialize their values.   dt=[0];
  DataRow[] myRow=();
  int i=0;
  int cl=;
  #region
// string FileTitle="<center><table><tr><td><b>Report Test</b></td></tr></table>"+"\n";//  string EndFile="&lt;/center&gt;";
//  //Web page definition  HttpResponse resp;
  resp=;
  ();
  =("utf-8");
  ("Content-disposition","attachment;filename="+ExportFileName+".doc");
  ="application/-word";
//   oSW=new StringWriter();
//    oHW=new (oSW);
//   oDG=new ();
//      =[0];
//  ();
//  (oHW);
//  (()+()+());
//  ();
  #endregion
  string BeginTab="&lt;table border='0' cellpadding='0' cellspacing='0' style='border-right:#000000 0.1pt solid;border-top:#000000 0.1pt solid;'&gt;";
  string EndTab="&lt;/table&gt;";
  string FileIO=null;
  string MainIO=null;
  string TitleTab="&lt;tr&gt;&lt;td style='font-size:13px;' align='center'&gt;&lt;b&gt;"+titlename+"&lt;/b&gt;&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td align='right' style='font-size:15px;'&gt;"+()+"Year"+()+"moon"+()+"Sunday&nbsp;&nbsp;&nbsp;&nbsp;</td></tr>";
  string BeginTr="&lt;tr&gt;";
  string EndTr="&lt;/tr&gt;";
  for(i=0;i&lt;cl;i++)
  {
  FileIO+="&lt;td style='border-left:#000000 0.1pt solid; border-bottom:#000000 1.0pt solid; font-size:15px;' align='center'&gt;&lt;b&gt;"+[i].()+"&lt;/b&gt;&lt;/td&gt;";
  }
  FileIO=()+()+();
  // Process data line by line  foreach(DataRow row in myRow)
  {
  string OutIO=null;
  //Current data is written  for(i=0;i&lt;cl;i++)
  {
   OutIO+="&lt;td style='border-left:#000000 0.1pt solid; border-bottom:#000000 1.0pt solid; font-size:15px;' align='center'&gt;"+row[i].ToString()+"&lt;/td&gt;";
  }
  MainIO+=()+()+();
  }
  FileIO="&lt;center&gt;&lt;table&gt;"+()+"&lt;tr&gt;"+()+()+()+()+"&lt;/tr&gt;&lt;/table&gt;&lt;/center&gt;";
  (());
  ();
 }
 #endregion
 }
}

For more information about C# related content, please check out the topic of this site:Summary of C# operation skills》、《Summary of XML file operation skills in C#》、《Tutorial on the usage of common C# controls》、《Summary of WinForm control usage》、《C# data structure and algorithm tutorial》、《Introduction to C# object-oriented programming tutorial"and"Summary of thread usage techniques for C# programming》

I hope this article will be helpful to everyone's C# programming.