C#winform从数据集导出Excel(带指定Excel样式) 转+修改

来源：互联网发布：mac系统快速回到桌面编辑：程序博客网时间：2024/05/16 15:07

利用数据集作为数据源，在winform中导出Excel。

1、首先要确保使用的机器安装Office工具。然后在工程中添加如下引用。

2、如果出现Microsoft.Office.Interop.Excel不存在可按下面方法处理

(1) 删除对带黄色感叹号的Excel的引用
(2) 在visual Studio 2005命令提示工具中，定位到Excel安装目录，运行“TlbImp EXCEL.EXE”命令，把Excel.exe编译为Excel.dll，
(3) 引用刚编译好的Excel.dll，

using Excel;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using Microsoft.Office.Interop;
using Excel;

using System.Reflection; //命名空间包含提供加载类型、方法和字段的有组织的视图的类和接口，具有动态创建和调用类型的功能
using System.Diagnostics; // 命名空间提供特定的类，使您能够与系统进程、事件日志和性能计数器进行交互

namespace UpdateClient
{
    public partial class frmExcel : Form
    {
        private DataSet ds;

        private Excel.Application myExcel = null;
        public frmExcel()
        {
            InitializeComponent();
        }

        private void FillDataset()
        {
            ds = new DataSet();
            SqlConnection cnn = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;password=");
            SqlDataAdapter da = new SqlDataAdapter("select * from Products", cnn);
            da.Fill(ds);
        }

        private List<Process> GetExcelProcesses()
        {
            Process[] processes = Process.GetProcesses();
            List<Process> ListProcess = new List<Process>();

            foreach (Process _pr in processes)
            {
                if (_pr.ProcessName.ToUpper().Equals("EXCEL"))
                {
                    ListProcess.Add(_pr);
                }
            }
            return ListProcess;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            List<Process> excelProcesses = GetExcelProcesses();
            if (excelProcesses.Count > 0)
            {
                MessageBox.Show("请将其他的Excel关闭再进行导出！");
                KillTheExcel();
                return;
            }

if (myExcel == null)
myExcel = new Excel.Application();

ExprotExcel2();
}

        private void ExprotExcel2()
        {
            //SaveFileDialog sfd = new SaveFileDialog();
            //sfd.Filter = "EXCEL文档(*.xls)|*.xls|所有文档(*.*)|*.*";
            //if (sfd.ShowDialog() != DialogResult.OK)
            //    return;
            //string savePatrh = sfd.FileName;

this.Cursor = Cursors.WaitCursor;
FillDataset();

            //命名空间包含定义区域性相关信息的类，这些信息包括语言、国家/地区、使用的日历、日期、货币和数字的格式模式以及字符串的排序顺序。
            //我们可以使用这些类编写全球化（国际化）应用程序。
            //而像 StringInfo 和 TextInfo 这样的类更是为我们提供了诸如代理项支持和文本元素处理等高级全球化功能。
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; //备份文化环境
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //设置文化环境

//Workbook workbookData = myExcel.Workbooks.Add(Missing.Value);
Workbook workbookData = myExcel.Application.Workbooks.Add(true);

//Workbook workbookData = myExcel.Application.Workbooks.Open(@"E:/111.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //打开已经在文件

Worksheet xlSheet = (Worksheet)workbookData.Worksheets[1];

            "内容"#region "内容"
            xlSheet.Cells[1,1] = "Northwind数据库产品表";
            WorksheetClass wsClass = new WorksheetClass();
            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).MergeCells = true;//合并单元格
            //设置主标题单元格的样式
            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平对齐方式
            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).VerticalAlignment = XlVAlign.xlVAlignCenter; //垂直对齐方式
            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Font.Bold = true;//字体加粗
            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Font.ColorIndex = 0; ;//字体颜色
            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Font.Italic = true;//是否斜体
            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Font.Size = 22; //字体大小
            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).Borders.LineStyle = XlLineStyle.xlContinuous;//边框样式
            xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, 10]).RowHeight = 33.75;//行高

            //填充标题
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                myExcel.Cells[2, i + 1] = ds.Tables[0].Columns[i].Caption;
            }

int rowCount = ds.Tables[0].Rows.Count;
int colCount = ds.Tables[0].Columns.Count;

xlSheet.get_Range(xlSheet.Cells[3, 1], xlSheet.Cells[rowCount + 2, colCount]).Borders.LineStyle = XlLineStyle.xlContinuous;//边框的样式

//设置填充单元格样式
object[,] objData = new object[rowCount, colCount];

            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 0; j < colCount; j++)
                {
                    objData[i, j] = ds.Tables[0].Rows[i][j].ToString();
                }
                System.Windows.Forms.Application.DoEvents();
            }
            Range range = xlSheet.get_Range(xlSheet.Cells[3, 1], xlSheet.Cells[rowCount + 2, colCount]);
            range.Value2 = objData; //将对象数组的值赋值给Excel

System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI; //恢复文化环境
#endregion

            try
            {
                //myExcel.Save(@"C:a.xls"); //此方法有问题
                workbookData.Saved = true;
                workbookData.SaveCopyAs(@"E:/a111.xls");
                myExcel.Quit();
                //myExcel.Visible = true;
                MessageBox.Show("导出成功！", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookData);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
                GC.Collect();
                range = null;
                xlSheet = null;
                workbookData = null;
                myExcel = null;
                this.Cursor = Cursors.Default;
                //KillTheExcel();
            }
        }
        private void KillTheExcel()
        {
            List<Process> listProcess = GetExcelProcesses();
            foreach (Process _pr in listProcess)
            {
                _pr.Kill();
            }
        }
        private void frmExcel_Load(object sender, EventArgs e)
        {

}

        private void button2_Click(object sender, EventArgs e)
        {
            string[,] myData =
            {
              {"车牌号","类型","品牌","型号","颜色","附加费证号","车架号"},
              {"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"},
              {"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"},
              {"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"},
              {"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"},
              {"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"},
              {"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"},
              {"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"},
              {"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"},
              {"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"},
              {"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"}
            };

            //创建一个Excel文件
            Excel.Application myExcel = new Excel.Application();
            myExcel.Application.Workbooks.Add(true);

//让Excel文件可见
myExcel.Visible = true;

//第一行为报表名称
myExcel.Cells[1, 4] = "普通报表";

            //逐行写入数据，
            for (int i = 0; i < 11; i++)
            {
                for (int j = 0; j < 7; j++)
                {
                    //以单引号开头，表示该单元格为纯文本
                    myExcel.Cells[2 + i, 1 + j] = "'" + myData[i, j];
                }
            }
        }
    }
}
转自:http://www.cnblogs.com/commonname/articles/876167.html