利用CloseXML导出EXCEL

来源：互联网发布：paxos算法细节详解编辑：程序博客网时间：2024/06/05 05:42

1. 配置文件：

<?xml version="1.0" encoding="utf-8" ?><configuration>  <appSettings>    <!--连接串 -->    <add key="ConnString" value="Data Source=(local)\sqlserver2014;Initial Catalog=master;Integrated Security=True;" />    <!--取数据 sql , 大于用 > 代替, 小于用 < 代替 -->    <add key="SQL" value="select [number] from master.dbo.spt_values WHERE [TYPE]='P' AND number<25"/>    <!--输出的excel文件的名称的前面的部分（后面程序自动加时间代替）-->    <add key="FileName" value="" />    <!--输出的路径 -->    <add key="Directory" value="d:\" />    <!--是否调试状态 (1/0) 。为1时输出信息并且最终会暂停，为0时不输出信息且操作完不暂停 -->    <add key="Debug" value="1"/>  </appSettings></configuration>

2. Program.cs

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Data;namespace ExportExcel{    class Program    {        static bool debug = true;        static void Main(string[] args)        {            try            {                string connString = System.Configuration.ConfigurationSettings.AppSettings["ConnString"];                string sql = System.Configuration.ConfigurationSettings.AppSettings["SQL"];                string fileName = System.Configuration.ConfigurationSettings.AppSettings["FileName"] + DateTime.Now.ToString("yyyyMMdd_HHmmss")+".xlsx";                string directory = System.Configuration.ConfigurationSettings.AppSettings["Directory"];                string fullPath = string.Format("{0}\\{1}", directory, fileName);                debug = System.Configuration.ConfigurationSettings.AppSettings["Debug"] == "1";                ConsoleWriteLine("配置项：");                ConsoleWriteLine("SQL: {0}", sql);                ConsoleWriteLine("FileName: {0}", fileName);                ConsoleWriteLine("Directory: {0}", directory);                ConsoleWriteLine("输出完整路径: {0}\r\n" , fullPath );                DataTable dt = new DataTable();                dt.TableName = fileName;                DateTime beginT = DateTime.Now;                using (SqlConnection conn = new SqlConnection(connString))                {                    conn.Open();                    ConsoleWriteLine("1.连接成功！");                    SqlCommand cmd = new SqlCommand(sql, conn);                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);                    adapter.Fill(dt);                    ConsoleWriteLine("2.取数据成功！");                    using (var wb = new ClosedXML.Excel.XLWorkbook())                    {                        wb.Worksheets.Add(dt);                        wb.SaveAs(fullPath);                    }                    ConsoleWriteLine("3.已输出文件至: {0} , 消耗秒数: {1} " , fullPath , DateTime.Now.Subtract(beginT).TotalSeconds );                }                ConsoleWriteLine("End");                if (debug)                {                    Console.Read();                }            }            catch (Exception ex)            {                Console.WriteLine(ex.Message);            }        }        static void ConsoleWriteLine(string formatStr, params object[] objArr)         {            if (debug)            {                Console.WriteLine(formatStr, objArr);            }        }    }}