Excel编程之Dataset导出到excel

来源：互联网发布：淘宝对实体店的影响编辑：程序博客网时间：2024/09/21 08:50
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;   }}
1 0