复杂EXCEL处理(DataSet里的值导入EXCEL)

定义private Excel.ApplicationClass _excel;

private void SaveToXls(string fileName,DataSet ds)

{

     _excel = new Excel.ApplicationClass();

     System.Data.DataTable table = ds.Tables[0];

     Excel.WorkbookClass wb = (Excel.WorkbookClass)this._excel.Workbooks.Add(System.Reflection.Missing.Value);

 

     int colIndex  = 0; //控制EXECL行数

     int callNum = 0;      //控制EXCEL的列数

 

     foreach(DataRow dr in table.Rows)

     {

          int row = int.Parse(dr["DisplayOrder"].ToString());

          int call = int.Parse(dr["mergeNum"].ToString());

          if (colIndex != row)

          {

                       callNum = 1;

                       colIndex ++;

          }

                  

           _excel.Cells[row,callNum] = dr["SortName"].ToString();

           if (call > 1)

           {

                       int endY = callNum + call - 1;

                       mergeCells(row,callNum,row,endY); //合并单元格

           }

           if (call == 1)

           {

                       Regex rVar = new Regex("####");

                       string[] answer = rVar.Split(dr["Answer"].ToString());

                       for (int i=0;i<answer.Length;i++)

                       {

                            int aRow = row + 1;

                            _excel.Cells[aRow,callNum] = answer[i].ToString();

                       }

             }

             callNum = callNum + call;

      }

      wb.Saved = true;

      this._excel.ActiveWorkbook.SaveCopyAs(Server.MapPath(fileName));

}

 

合并单元格方法

public void mergeCells(int startX, int startY, int endX, int endY)

{

              _excel.get_Range(_excel.Cells[startX, startY], _excel.Cells[endX, endY]).MergeCells = true;

}