Excel导出功能

来源：互联网发布：java中json数组遍历编辑：程序博客网时间：2024/06/06 10:49
值得收藏的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;            }        }    }}
1 0