Excel导出功能

最新推荐文章于 2024-06-11 23:03:41 发布

原创最新推荐文章于 2024-06-11 23:03:41 发布 · 316 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #导出 #Datatable #导入导出

.net 同时被 3 个专栏收录

27 篇文章

订阅专栏

代码

26 篇文章

订阅专栏

Jquery

11 篇文章

订阅专栏

本文介绍了一种使用C#实现从数据库读取数据并导出到Excel的方法。通过ADO.NET连接数据库，利用DataTable存储查询结果，并借助Microsoft.Office.Interop.Excel组件完成Excel文件的创建与填充。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

值得收藏的Excel导出功能：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Office;
using Microsoft.Office.Interop.Excel;

namespace WebUI.ExcelOutIn
{
    /// <summary>
    /// ExcelIn 的摘要说明
    /// </summary>
    public class ExcelIn : IHttpHandler
    {
        System.Data.DataTable dt = new System.Data.DataTable();

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";

            using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Test;Integrated Security=True"))
            {
                conn.Open();
                using (IDbCommand comm = conn.CreateCommand())
                {
                    SqlDataAdapter da = new SqlDataAdapter("select * from Users", conn);
                   
                    da.Fill(dt);

                    if (ExportExcel("Users", dt, "E:\\")) {
                        context.Response.Write("导出成功!");
                    }
                }
            }
        }

        public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
        {
            bool fileSaved = false;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            worksheet.Name = "用户表";
            worksheet.Cells.Font.Size = 10;
            Microsoft.Office.Interop.Excel.Range range;

            long totalCount = dt.Rows.Count; //表行数
            long rowRead = 0;
            float percent = 0;

            //写入字段
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true; //加粗

            }
            //写入数值
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }
 
            //保存文件

            workbook.Saved = true;
            workbook.SaveCopyAs(saveFileName + reportName+".xls");
            fileSaved = true;

           
            xlApp.Application.Workbooks.Close();
            xlApp.Quit();
           
            GC.Collect();
            return fileSaved;
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}