c#导出excel支持多sheet导出，可自定义sheetName

最新推荐文章于 2020-05-22 15:53:42 发布

DenggLin

最新推荐文章于 2020-05-22 15:53:42 发布

阅读量3.7k

点赞数 1

分类专栏： c# C#从入门到出门文章标签： excel c#

本文链接：https://blog.csdn.net/qq_34117170/article/details/78055031

版权

c# 同时被 2 个专栏收录

17 篇文章 0 订阅

订阅专栏

C#从入门到出门

7 篇文章 0 订阅

订阅专栏

直接贴代码，总算明白了

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.IO;


namespace ReadMailPop
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = ConfigurationManager.ConnectionStrings["db_conn"].ConnectionString;
            SqlConnection sConn = new SqlConnection(connString);
            DataSet dtSet = new DataSet(); //数据集DataSet
            try
            {
                sConn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("error：" + ex.Message);

            }
            ExportLog("============start============");
            string sql = File.ReadAllText(GetPath("comsql.sql"));
            ExportLog(sql);
            SqlCommand sCmd = new SqlCommand(sql, sConn);
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sCmd);//数据适配器DataAdapter.数据适配器根据不同的连接模式也有不同的形式：SqlDataAdapter、 OleDbDataAdapter和OdbcDataAdapter。
            sqlAdapter.Fill(dtSet);
            System.Data.DataTable Table =dtSet.Tables[0];
            string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "excel", "excel" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm") + ".xlsx");
            File.AppendAllText(fileName, "");
            ExportLog("start export excel");
            DataTabletoExcel(Table, fileName);
            ExportLog("============End All==========");
        }
        private static string GetPath(string fileName)
        {
            return Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sql", fileName);
        }
         private static void ExportLog(string msg)
        {

            Console.WriteLine(msg);
            string path = @"log\log" + DateTime.Now.ToString("yyyy-MM-dd").ToString() + ".txt";
            msg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.ffff") + "\t" + msg + "\r\n";
            File.AppendAllText(path, msg);

        }

        public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
        {

            if (tmpDataTable == null)
            {
                ExportLog("=========empty data=========");
                return;
            }  
            int rowNum = tmpDataTable.Rows.Count;
            int columnNum = tmpDataTable.Columns.Count;
            int rowIndex = 1;
            int columnIndex = 0;
            Application xlApp = new Application();
            if (xlApp == null)
            {
                Console.WriteLine("don't create excel, don't install excel");
                return;
            }
            xlApp.DefaultFilePath = "";
            xlApp.DisplayAlerts = true;
            xlApp.SheetsInNewWorkbook = 2;
            Workbook xlBook = xlApp.Workbooks.Add(true);
            Worksheet sheet = (Worksheet)xlBook.Worksheets[1];

            sheet.Copy(Type.Missing, (Worksheet)xlBook.Worksheets[1]);
            sheet.Name = "update before";
            Worksheet sheet2 = (Worksheet)xlBook.Worksheets[2];
            sheet2.Name = "update After";
            foreach (DataColumn dc in tmpDataTable.Columns)  //将DataTable的列名导入Excel表第一行  
            {
                columnIndex++;
                sheet2.Cells[rowIndex, columnIndex] = dc.ColumnName;
            }            //将DataTable中的数据导入Excel中 

            for (int i = 0; i < rowNum; i++)
            {
                rowIndex++; columnIndex = 0;
                for (int j = 0; j < columnNum; j++)
                {
                    columnIndex++;
                    sheet2.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
                }
            }          //xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));

            ExportLog("export fileurl"+ strFileName);
            xlBook.SaveCopyAs(strFileName);  

        }

     }
}