C# 将DataSet保存为EXCEl 模板

来源：互联网发布：电玩巴士淘宝3ds 编辑：程序博客网时间：2024/05/16 11:16

方便好用的类模板，传入dataset 就可以生成EXCEL。要添加引用Microsoft.Office.Interop.Excel.dll

class ToExcel
    {
          //  excelName为保存的EXCEL名称
        public void ToExl(DataSet ds, string excelName)
        {

            int tablecount = ds.Tables.Count;
            if (tablecount <= 0) return ;

            int index = 0;
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Microsoft.Office.Interop.Excel.Workbook xlBook = null;
            Microsoft.Office.Interop.Excel.Worksheet xlSheet = null;
            int rowIndex = 1;
            int colIndex = 0;
            object missing = Missing.Value;

            try
            {
                    xlApp = new Microsoft.Office.Interop.Excel.Application();
                    xlBook = xlApp.Workbooks.Add(true);

                    foreach (System.Data.DataTable dt in ds.Tables)
                    {


                            index++;
                            rowIndex = 1;
                            colIndex = 0;

xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets[1];

xlSheet.Name = dt.TableName;

                            foreach (DataColumn Col in dt.Columns)
                            {
                                colIndex = colIndex + 1;
                                xlApp.Cells[1, colIndex] = "   " + Col.ColumnName;
                            }

                            int rowNum = dt.Rows.Count;
                            int colNum = dt.Columns.Count;
                            string[,] finalData = new string[rowNum, colNum];
                            for (int i = 0; i < rowNum; i++)
                            {
                                for (int j = 0; j < colNum; j++)
                                {
                                    finalData[i, j] = dt.Rows[i][j].ToString();
                                }
                            }

                            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, colIndex]).Font.Bold = true;
                            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
                            xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]).Value2 = finalData;
                            xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]).NumberFormatLocal = "@ ";

if (index < tablecount) xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets.Add(missing, missing, missing, missing);
}

//保存的路径，可以根据要求更改

                if (System.IO.File.Exists(Environment.CurrentDirectory + "//" + excelName + ".xls")) System.IO.File.Delete(Environment.CurrentDirectory + "//" + excelName + ".xls");
                xlApp.Visible = false;
                xlSheet.SaveAs(Environment.CurrentDirectory + "//" + excelName + ".xls", missing, missing, missing, missing, missing, missing, missing, missing, missing);

            }
            finally
            {
                object saveChange = true;
                xlBook.Close(saveChange, Environment.CurrentDirectory + "//" + excelName + ".xls", missing);
                xlApp.Quit();
                if (xlSheet != null) Marshal.ReleaseComObject(xlSheet);
                if (xlBook != null) Marshal.ReleaseComObject(xlBook);
                if (xlApp != null) Marshal.ReleaseComObject(xlApp);
                xlApp = null;
                xlBook = null;
                xlSheet = null;
                GC.Collect();

            }
        }

}