导出任意形式Excel(共享心得)

參考: http://topic.csdn.net/u/20091113/10/77a993dc-af9a-46a3-ab99-0c5050977d93.html?81131

 

C# code

  
  

   
   
   
    
   
   ///
   
    
   
   <summary>
   
   
    
   
   ///
   
    生成EXCEL文件
    
   
   ///
   
    _til是表格的标题，_row是表格的行数,_col是表格的列数,_dt是数据表,_filename是保存的EXCEL表的名称
    
   
   ///
   
    
   
   </summary>
   
   

   
       
   
   public
   
    
   
   void
   
    SaveToExcel(
   
   string
   
    _til, 
   
   int
   
    _row, 
   
   int
   
    _col, DataTable _dt, 
   
   string
   
    _filename)
    {
        Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet 
   
   =
   
    
   
   new
   
    Microsoft.Office.Interop.Owc11.SpreadsheetClass();
        
   
   //
   
   合并单元格
   
   

   
           xlsheet.get_Range(xlsheet.Cells[
   
   1
   
   , 
   
   1
   
   ], xlsheet.Cells[
   
   1
   
   , _col]).set_MergeCells(
   
   true
   
   );
        
   
   //
   
    xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[3, 2]).set_MergeCells(true);
   
   

   
           xlsheet.ActiveSheet.Cells[
   
   1
   
   , 
   
   1
   
   ] 
   
   =
   
    _til;
        
   
   //
   
   字体加粗
   
   

   
           xlsheet.get_Range(xlsheet.Cells[
   
   1
   
   , 
   
   1
   
   ], xlsheet.Cells[
   
   1
   
   , _col]).Font.set_Bold(
   
   true
   
   );
        
   
   //
   
   单元格文本水平居中对齐
   
   

   
           xlsheet.get_Range(xlsheet.Cells[
   
   1
   
   , 
   
   1
   
   ], xlsheet.Cells[_row 
   
   +
   
    
   
   2
   
   , _col]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
        
   
   //
   
   设置字体大小
   
   

   
           xlsheet.get_Range(xlsheet.Cells[
   
   1
   
   , 
   
   1
   
   ], xlsheet.Cells[
   
   1
   
   , _col]).Font.set_Size(
   
   14
   
   );
        
   
   //
   
   设置列宽
        
   
   //
   
   xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[1, 3]).set_ColumnWidth(50);

        
   
   //
   
   画边框线
   
   

   
           xlsheet.get_Range(xlsheet.Cells[
   
   1
   
   , 
   
   1
   
   ], xlsheet.Cells[_row 
   
   +
   
    
   
   2
   
   , _col]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);

        
   
   //
   
   定义一个2维数组用来存储DATATABLE里的数据
   
   

   
           
   
   object
   
   [,] dataArray 
   
   =
   
    
   
   new
   
    
   
   object
   
   [_row, _col];
        
   
   //
   
   添加列名
   
   

   
           
   
   for
   
    (
   
   int
   
    k 
   
   =
   
    
   
   0
   
   ; k 
   
   <
   
    _dt.Columns.Count; k
   
   ++
   
   )
        {
            xlsheet.ActiveSheet.Cells[
   
   2
   
   , k 
   
   +
   
    
   
   1
   
   ] 
   
   =
   
    _dt.Columns[k].Caption.ToString();
        }
        
   
   //
   
   把DATATABLE里的数据导到2维数组中
   
   

   
           
   
   for
   
    (
   
   int
   
    i 
   
   =
   
    
   
   0
   
   ; i 
   
   <
   
    _row; i
   
   ++
   
   )
        {
            
   
   for
   
    (
   
   int
   
    j 
   
   =
   
    
   
   0
   
   ; j 
   
   <
   
    _col; j
   
   ++
   
   )
            {
                dataArray[i, j] 
   
   =
   
    _dt.Rows[i][j];
            }
        }
        
   
   //
   
   把2维数组中的数据导到EXCEL中
   
   

   
           xlsheet.get_Range(
   
   "
   
   A3
   
   "
   
   , xlsheet.Cells[_row 
   
   +
   
    
   
   2
   
   , _col]).Value2 
   
   =
   
    dataArray;
        xlsheet.Export(_filename, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
    }
    
   
   ///
   
    
   
   <summary>
   
   
    
   
   ///
   
    导入EXCEL表
    
   
   ///
   
    
   
   </summary>
   
   
    
   
   ///
   
    
   
   <param name="strFileName"></param>
   
   
    
   
   ///
   
    
   
   <returns></returns>
   
   

   
       
   
   public
   
    DataSet CreateDataSource(
   
   string
   
    strFileName, 
   
   string
   
    Sqlstr)
    {
        
   
   string
   
    strCon 
   
   =
   
    
   
   "
   
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
   
   "
   
    
   
   +
   
    strFileName 
   
   +
   
    
   
   "
   
   ; Extended Properties=Excel 8.0;
   
   "
   
   ;
        OleDbConnection olecon 
   
   =
   
    
   
   new
   
    OleDbConnection(strCon);
        OleDbDataAdapter myda 
   
   =
   
    
   
   new
   
    OleDbDataAdapter(Sqlstr, olecon);
        DataSet myds 
   
   =
   
    
   
   new
   
    DataSet();
        myda.Fill(myds);
        
   
   return
   
    myds;
    }