c# .Net :Excel NPOI导入导出操作教程之数据库表信息数据导出到一个Excel文件并写到磁盘示例分享

时间:2024-08-07 09:03:32

    string sql = @"select * from T_Excel";

————————————————DataTable Star————————————————
        DataTable dt = SqlHelper.ExecuteDataTable(sql);
        if (dt.Rows.Count > 0)
        {
            //创建工作簿
            IWorkbook workbook = new HSSFWorkbook();
            //创建表
            ISheet sheet = workbook.CreateSheet("DBToExcel");
            IRow row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("id信息");
            row0.CreateCell(1).SetCellValue("名称信息");
            row0.CreateCell(2).SetCellValue("备注信息");
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                //创建行接受DataTable的行数据
                IRow row = sheet.CreateRow(r + 1);
                row.CreateCell(0).SetCellValue((int)dt.Rows[r]["Id"]);
                row.CreateCell(1).SetCellValue(dt.Rows[r]["Name"].ToString());
                row.CreateCell(2).SetCellValue(dt.Rows[r]["Remarks"].ToString());

            }

————————————————DataTable END————————————————

————————————————SqlDataReader ————————————————
            //SqlDataReader reader = SqlHelper.ExecuteReader(sql);
            //if (reader.HasRows)
            //{
            //    //创建工作簿
            //    IWorkbook workbook = new HSSFWorkbook();
            //    //创建工作表
            //    ISheet sheet = workbook.CreateSheet("DBToExcel");
            //    int rowIndex = 0;
            //    while (reader.Read())
            //    {
            //        //读取一条数据就创建一行row
            //        IRow row = sheet.CreateRow(rowIndex);
            //        //记录读取数据行数
            //        rowIndex++;
            //        row.CreateCell(0).SetCellValue(reader.GetInt32(0));
            //        row.CreateCell(1).SetCellValue(reader.GetString(1));
            //        row.CreateCell(2).SetCellValue(reader.GetString(2));
            //    }

————————————————SqlDataReader ————————————————
            using (FileStream saveurl = File.OpenWrite(@"C:\Users\Administrator\Desktop\112.xls"))
            {
                workbook.Write(saveurl);
                Title = "已经导出数据!";
            }

        }
        else
        {
            Title = "没有导出任何数据!";
        }

string sql = @"select * from T_Excel";
        DataTable dt = SqlHelper.ExecuteDataTable(sql);
        if (dt.Rows.Count > 0)
        {
            //创建工作簿
            IWorkbook workbook = new HSSFWorkbook();
            //创建表
            ISheet sheet = workbook.CreateSheet("DBToExcel");
            IRow row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("id信息");
            row0.CreateCell(1).SetCellValue("名称信息");
            row0.CreateCell(2).SetCellValue("备注信息");
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                //创建行接受DataTable的行数据
                IRow row = sheet.CreateRow(r + 1);
                row.CreateCell(0).SetCellValue((int)dt.Rows[r]["Id"]);
                row.CreateCell(1).SetCellValue(dt.Rows[r]["Name"].ToString());
                row.CreateCell(2).SetCellValue(dt.Rows[r]["Remarks"].ToString());

            }

            //SqlDataReader reader = SqlHelper.ExecuteReader(sql);
            //if (reader.HasRows)
            //{
            //    //创建工作簿
            //    IWorkbook workbook = new HSSFWorkbook();
            //    //创建工作表
            //    ISheet sheet = workbook.CreateSheet("DBToExcel");
            //    int rowIndex = 0;
            //    while (reader.Read())
            //    {
            //        //读取一条数据就创建一行row
            //        IRow row = sheet.CreateRow(rowIndex);
            //        //记录读取数据行数
            //        rowIndex++;
            //        row.CreateCell(0).SetCellValue(reader.GetInt32(0));
            //        row.CreateCell(1).SetCellValue(reader.GetString(1));
            //        row.CreateCell(2).SetCellValue(reader.GetString(2));
            //    }
            using (FileStream saveurl = File.OpenWrite(@"C:\Users\Administrator\Desktop\112.xls"))
            {
                workbook.Write(saveurl);
                Title = "已经导出数据!";
            }

        }
        else
        {
            Title = "没有导出任何数据!";
        }