Excel编程之Dataset导出到excel

public class DataSetToExcel
{
///
/// 将数据集中的数据导出到EXCEL文件,可导出乡村代码
///
/// 输入数据集
/// 是否显示该EXCEL文件
/// 保存路径
///
public static bool DStoExcel(DataSet dataSet, bool isShowExcle, string excelFileFullPath)
{
//获取Dataset中的一个表
DataTable dataTable = dataSet.Tables[0];//Dataset数据集中的第一个表

        int rowNumber = dataTable.Rows.Count;//行数，不包括字段名

        int columnNumber = dataTable.Columns.Count;//列数

        int colIndex = 0;

        if (rowNumber == 0)
        {

            return false;

        }

        //建立Excel对象

        Application excel = new Application();

        Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet

        );

        Worksheet worksheet = (Worksheet)workbook.Worksheets[1];

        excel.Visible = isShowExcle;

        //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];


        //生成字段名称（表头）
        foreach (DataColumn col in dataTable.Columns)//遍历表中所有列
        {
            colIndex++;
            if (colIndex ==4||colIndex==5)//获取4列或者5列表头，下标从1开始，1对应第一列
            {
                excel.Cells[1, colIndex-3] = col.ColumnName;//遍历第一行中各列
            }
            excel.Cells[1, 3] = "备注";//设置一个新的列，表头
        }
       //生成表的主体
        object[,] objData = new object[rowNumber, columnNumber];

        for (int r = 0; r < rowNumber; r++)//表的正文是以下标从0开始的，也就是标题栏下面是每行每列是从0开始的
        {

            for (int c = 3; c <=4; c++)//列，原始表的4和5列
            {

                objData[r, c-3] = dataTable.Rows[r][c];

            }

        }

        // 写入Excel

        var range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];//2行一列开始逐个写入

        range.NumberFormat = "@";//设置单元格为文本格式

        range.Value2 = objData;



        #region      设置表格式
        //设置表格中格式
        //var range1 = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]].NumberFormat = "yyyy-m-d h:mm";
       var range1 = worksheet.Range[excel.Cells[2, 5], excel.Cells[rowNumber + 1, 5]];
       range1.NumberFormat = "000000";//格式为邮政编码
       var range2 = worksheet.Range[excel.Cells[2, 2], excel.Cells[rowNumber + 1, 2]];//第八列全部设置为邮编格式
       range2.NumberFormat = "000000";
       range2.HorizontalAlignment = 2;//设置居中
       //var range3 = worksheet.Range[excel.Cells[2, 4], excel.Cells[rowNumber, 4]];
       //worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber, 1]].Insert(range3);
       #endregion
       //保存Excel文件
       //workbook.SaveCopyAs(excelFileFullPath);
            workbook.SaveAs(excelFileFullPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
             Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
             Missing.Value, Missing.Value);
        // @"E:\MyTemp\"
        return true;

    }
   /// <summary>
   /// 重写上面方法，只是没有保存到本地的功能
   /// </summary>
   /// <param name="dataSet"></param>
   /// <param name="isShowExcle"></param>
   /// <returns></returns>
   public static bool DStoExcel(DataSet dataSet, bool isShowExcle)
   {
       //获取Dataset中的一个表
       DataTable dataTable = dataSet.Tables[1];

       int rowNumber = dataTable.Rows.Count;//行数，不包括字段名

       int columnNumber = dataTable.Columns.Count;//列数

       int colIndex = 0;

       if (rowNumber == 0)
       {

           return false;

       }
       //建立Excel对象

        Application excel = new Application();

        Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet

        );

        Worksheet worksheet = (Worksheet)workbook.Worksheets[1];

        excel.Visible = isShowExcle;

        //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];


        //生成字段名称（表头）
        foreach (DataColumn col in dataTable.Columns)
        {
            colIndex++;
            if (colIndex<=8)//一直到H（8）列
            {
                excel.Cells[1, colIndex] = col.ColumnName;//遍历第一行中各列
            }                                
        }

        object[,] objData = new object[rowNumber, columnNumber];

        for (int r = 0; r < rowNumber; r++)//表的正文是以下标从0开始的，也就是标题栏下面是每行每列是从0开始的
        {

            for (int c = 0; c <=7; c++)//列
            {

                objData[r, c] = dataTable.Rows[r][c];

            }

        }

        // 写入Excel

        var range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];//2行一列开始逐个写入

        range.NumberFormat = "@";//设置单元格为文本格式

        range.Value2 = objData;
        //var range1 = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]].NumberFormat = "yyyy-m-d h:mm";
       var range1 = worksheet.Range[excel.Cells[2, 5], excel.Cells[rowNumber + 1, 5]];
       range1.NumberFormat = "000000";//格式为邮政编码
       var range2 = worksheet.Range[excel.Cells[2, 8], excel.Cells[rowNumber + 1, 8]];//第八列全部设置为邮编格式
       range2.NumberFormat = "000000";

        // @"E:\MyTemp"
        return true;

    }

   /// <summary>
   /// 可以将DataSet中某个Datatable导入到excel中，便于查看
   /// </summary>
   /// <param name="dt">datatable</param>
   /// <param name="isOpen">是否打开</param>
   /// <returns></returns>
   public static bool DatataleToExcel(DataTable dt, bool isOpen)
   {
       if (dt != null)
       {
           object obj = Missing.Value;
           //建立一个新的excel,并在其中添加workbook
           Microsoft.Office.Interop.Excel.Application excel = new ApplicationClass();
           Workbook workbook = excel.Workbooks.Add(true);
           Worksheet xlSheet = (Worksheet)workbook.Worksheets[1];
           xlSheet.Name = dt.TableName;

           int colIndex = 1;
           int rowCount = dt.Rows.Count;
           int colCount = dt.Columns.Count;

           //处理列名
           for (int i = 0; i < colCount; i++)
           {
               xlSheet.Cells[1,colIndex] = dt.Columns[i].ColumnName;
               colIndex++;
           }
           //处理行，填充到worksheet
           object[,] objData = new object[rowCount, colCount];               
           for (int i = 0; i < rowCount; i++)
           {
               for (int j = 0; j < colCount; j++)
               {
                   objData[i,j] = dt.Rows[i][j];
               }
           }
           //设置单元格格式
           var range = xlSheet.Range[excel.Cells[2, 1], excel.Cells[rowCount + 1, colCount]];
           range.Value2 = objData;
           range.NumberFormat = "@";//设置单元格为文本格式
           excel.Visible = isOpen;               
       }
       return true;
   }

}