• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

c#ExportToExcels

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Business;
using System.Runtime.InteropServices;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
/// <summary>
/// ExportToExcels 的摘要说明
/// </summary>
public class ExportToExcels
{
public ExportToExcels()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
    /// <summary>
    /// 导出到 Excel 文件
    /// </summary>
    /// <param name="rs">当前记录</param>
    /// <param name="flds">要导出的字段</param>
    /// <param name="fn">文件的路径文件名</param>
    /// <param name="dcfg">词典配置工具</param>
    public static void ExportToExcel(DataAccess.Record[] rs, DataAccess.Field[] flds, string fn, DictionaryConfigure dcfg)
    {
        Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();   // 建立Excel对象
        excel.Application.Workbooks.Add(true);         // Excel表为添加状态
        //for (int i = 0; i < flds.Length; i++)          // 填充表头
        //{
        //    excel.Cells[1, i + 1] = flds[i].Name;
        //}
        excel.Columns.EntireColumn.AutoFit();//列宽自适应。
        excel.Cells[1, 1] = "2012年省级政府投资项目储备库项目申报汇总表";
        Microsoft.Office.Interop.Excel.Range r;
        r = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 19]); //取得合并的区域 
        r.MergeCells = true;
        // 设置整个报表的标题格式 
        //excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Bold = true;
        excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 18;
        // 设置整个报表的标题为跨列居中 
        excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Select();
        excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
        excel = setExcel(excel, "填报单位", 2, 1, 3, 1, false, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "项目类别", 2, 2, 3, 2, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "项目名称", 2, 3, 3, 3, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "建设性质", 2, 4, 3, 4, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "建设起止年限", 2, 5, 3, 5, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "建设结束年限", 2, 6, 3, 6, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "项目建设单位", 2, 7, 3, 7, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "建设规模及主要内容", 2, 8, 3, 8, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "总投资(万元)", 2, 9, 3, 9, true, 58, 20, XlHAlign.xlHAlignCenter);
        //excel = setExcel(excel, "拟申请省级财政性资金", 2, 10, 2, 13, true, 58, 20, XlHAlign.xlHAlignCenter);
        //excel = setExcel(excel, "共计(万元)", 3, 10, 3, 10, false, 58, 20, XlHAlign.xlHAlignCenter);
        //excel = setExcel(excel, "申请年度", 3, 11, 3, 11, false, 58, 20, XlHAlign.xlHAlignCenter);
        //excel = setExcel(excel, "拟申请省级财政性专项资金名称", 3, 12, 3, 12, false, 58, 20, XlHAlign.xlHAlignCenter);
        //excel = setExcel(excel, "拟申请金额(万元)", 3, 13, 3, 13, false, 58, 20, XlHAlign.xlHAlignCenter);
        //excel = setExcel(excel, "拟申请省级财政性资金", 2, 10, 2, 13, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "共计(万元)", 2, 10, 3, 10, false, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "申请年度", 2, 11, 3, 11, false, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "拟申请省级财政性专项资金名称", 2, 12, 3, 12, false, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "拟申请金额(万元)", 2, 13, 3, 13, false, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "项目审批文号", 2, 14, 3, 14, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "项目进展情况", 2, 15, 3, 15, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "项目用地情况(亩)", 2, 16, 3, 16, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "项目建设必要性", 2, 17, 3, 17, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "产业政策、城乡规划、土地利用规划符合性", 2, 18, 3, 18, true, 58, 20, XlHAlign.xlHAlignCenter);
        excel = setExcel(excel, "处理状态", 2, 19, 3, 19, true, 58, 20, XlHAlign.xlHAlignCenter);
        for (int i = 0; i < rs.Length; i++)            // 填充数据
        {
            for (int j = 0; j < flds.Length; j++)
            {
                string txt = rs[i].getStringValue(flds[j].ID);
                if (!flds[j].DictionaryID.Equals(""))
                {
                    txt = Business.DictionaryConfigure.GetCatalogNameByDictionary(txt, dcfg.getDictionary(flds[j].DictionaryID));
                }
                if (flds[j].EditIsDate)
                {
                    txt = (txt.Length > 8 ? txt.Substring(0, 8) : txt);
                    txt = Utility.StringUtility.Alt(txt, "????-??-??", "?");
                }
                excel.Cells[i + 3, j + 1] = txt;
            }
        }
        excel.Visible = false;
        excel.DisplayAlerts = false;
        excel.ActiveWorkbook.SaveAs(fn, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        KillExcelProcess(excel);
    }
    public static Microsoft.Office.Interop.Excel.ApplicationClass setExcel(Microsoft.Office.Interop.Excel.ApplicationClass excel, string title, int xNum, int yNum, int xNum2, int yNum2,bool isMerge, int height, int width, XlHAlign xl)
    {
        excel.Cells[xNum, yNum] = title;
        Microsoft.Office.Interop.Excel.Range r;
        r = excel.get_Range(excel.Cells[xNum, yNum], excel.Cells[xNum2, yNum2]);
        //r.MergeCells = true;
        r.Merge(isMerge);
        //r.ColumnWidth = width;
        //r.RowHeight = height;
        r.HorizontalAlignment = xl;
        r.WrapText = true;
        return excel;
    }
    /// <summary>
    /// 获取进程标识
    /// </summary>
    /// <param name="hwnd">输入参数:句柄</param>
    /// <param name="ID">输出参数:进程标识</param>
    /// <returns></returns>
    [DllImport("User32.dll", CharSet = CharSet.Auto)]
    public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
    /// <summary>
    /// 关闭 Excel 进程
    /// </summary>
    /// <param name="excel"></param>
    private static void KillExcelProcess(Microsoft.Office.Interop.Excel.ApplicationClass excel)
    {
        //------------ 方法 1 -----------------
        //excel.Quit();
        //excel = null;
        //GC.Collect();                                            // 垃圾回收
        //------------ 方法 2 -----------------
        try
        {
            IntPtr handler = new IntPtr(excel.Hwnd);     // 句柄
            int processid = 0;                           // 进程标识
            GetWindowThreadProcessId(handler, out processid);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(processid);  // 进程
            p.Kill();             // 杀除进程
        }
        catch
        {
            excel.Workbooks.Close();
            excel.Quit();
        }
        //-----------------------------
    }
    /// <summary>
    /// 从指定的Excel文件导入
    /// </summary>
    /// <param name="strFileName">导入文件</param>
    /// <returns></returns>
    public DataSet importFromExcel(string strFileName)
    {
        if (strFileName == "") return null;
        string strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + strFileName + ";Extended Properties=Excel 8.0";
        OleDbDataAdapter excelDA = new OleDbDataAdapter("select * from [Sheet1$]", strConn);      // 连接字符串
        DataSet ds = new DataSet();        // 建立数据集,用于存放导入Excel的数据
        excelDA.Fill(ds, "ExcelInfo");     // 填充DataSet
        return ds;
    }
    /// <summary>
    /// 将记录集导出到 Excel 文件
    /// </summary>
    /// <param name="rs">当前记录集</param>
    /// <param name="flds">要导出的字段</param>
    /// <param name="fn">文件的路径文件名</param>
    public static void RecordsToExcel(DataAccess.Record[] rs, DataAccess.Field[] flds, string fn)
    {
        Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();   // 建立Excel对象
        excel.Application.Workbooks.Add(true);         // Excel表为添加状态
        for (int i = 0; i < flds.Length; i++)          // 填充表头
        {
            excel.Cells[1, i + 1] = flds[i].Name;
        }
        for (int i = 0; i < rs.Length; i++)            // 填充数据
        {
            for (int j = 0; j < flds.Length; j++)
            {
                excel.Cells[i + 2, j + 1] = rs[i].getStringValue(flds[j].ID);
            }
        }
        excel.Visible = false;
        excel.DisplayAlerts = false;
        excel.ActiveWorkbook.SaveAs(fn, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        KillExcelProcess(excel);
    }
}

鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
C#BitArray(转载)发布时间:2022-07-14
下一篇:
C语言语法之占用字节发布时间:2022-07-14
热门推荐
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap