从DataSet 导出到Excel(是DataSet中的每个DataTable对应每个Sheet)

先来主要代码：

        public static void DataTableToExcel(DataSet dt, string Filename)
        {
            SaveToFile(ToExcel(dt), Filename);
        }

从上往下哈↓↓↓↓↓↓↓↓↓↓↓↓

        private static void SaveToFile(MemoryStream ms, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();

                fs.Write(data, 0, data.Length);
                fs.Flush();

                data = null;
            }
        }

使用NPOI来操作Excel:

        /// <summary>
        /// dataset To Excel
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        private static MemoryStream ToExcel(DataSet ds)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = new HSSFWorkbook();
            foreach (DataTable table in ds.Tables)
            {
                using (table)
                {
                    ISheet sheet = workbook.CreateSheet(table.TableName);
                    IRow headerRow = sheet.CreateRow(0);

                    //handling header.
                    foreach (DataColumn column in table.Columns)
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value

                    //handling value.
                    int rowIndex = 1;

                    foreach (DataRow row in table.Rows)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);

                        foreach (DataColumn column in table.Columns)
                        {
                            dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                        }

                        rowIndex++;
                    }
                }

                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
            }
            return ms;
        }

最后是调用的方法，也就是入口：

private void Button5_Click(object sender, EventArgs e)
        {
            SaveFileDialog opf = new SaveFileDialog();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            dt.Columns.Add("编号");   //"编号"
            dt.Columns.Add("品名");   //"品名"
            dt.Columns.Add("规格");   //"规格"
            dt.Columns.Add("单位");   //"单位"
            dt.Columns.Add("申请数量");   //"申请数量
            dt.Columns.Add("描述");   //"描述"
            dt.TableName = "sheet1";
            ds.Tables.Add(dt.Copy());
            dt.TableName = "sheet2";
            ds.Tables.Add(dt.Copy());
            dt.TableName = "sheet3";
            ds.Tables.Add(dt.Copy());
            opf.Filter = "Excel93-07文件（*.xls）|*.xls";
            if (DialogResult.OK.Equals(opf.ShowDialog()))
            {
                string localfilepath = opf.FileName.ToString();
                string fileNameExt = localfilepath.Substring(localfilepath.LastIndexOf("\\") + 1); //获取文件名，不带路径
                string[] names = fileNameExt.Split('.');
                string FilePath = localfilepath.Substring(0, localfilepath.LastIndexOf("\\"));
                string newFileName = FilePath + "\\" + names[0] + "_" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "." + names[1];     //fileNameExt   DateTime.Now.ToString("yyyyMMdd")
                RenderToExcel.DataTableToExcel(ds, newFileName);
                MessageBox.Show( "导出完成！", "提示");
            }
        }

意思就是这个意思，顺带把引用也贴上：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using ICSharpCode.SharpZipLib.Core;

这个四个引用，缺一不可，关于NPOI大家自行百度 

导入以上四个引用即可。

 

转载于:https://www.cnblogs.com/suchi/p/4522774.html