下载安装OfficeOpenXml模块
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection.Emit;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using OfficeOpenXml;
using OfficeOpenXml.Style;namespace WfrmOfficeOpenXml
{public partial class Form1 : Form{public static string filePath = Regex.Match(System.IO.Directory.GetCurrentDirectory(), @".*(?=:\\)").Value;public static string filePathExcel = filePath + ":\\Content\\Excel";public Form1(){InitializeComponent();label2.Text = filePathExcel;}/// <summary>/// ExcelPackage 保存为Excel文件/// </summary>public static void Test1(){string strFile = filePathExcel + "\\a.xlsx";if (!Directory.Exists(filePathExcel)){Directory.CreateDirectory(filePathExcel);}ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;using (FileStream fileStream = new FileStream(strFile, FileMode.Create))using (ExcelPackage package = new ExcelPackage(fileStream)){var sheet = package.Workbook.Worksheets.Add("木屋");//添加一个sheetvar cells = sheet.Cells;cells[1, 1].Value = "大大怪将军的小木屋";//给第一行第一列赋值cells["A1:I1"].Style.Font.Bold = true; //指定区域设置字体加粗cells["A1:I1"].Style.Font.Size = 18; //指定区域设置字体加粗cells["A1:I1"].Merge = true;//将A列1行和I列1行的单元格合并//列自适应宽度sheet.Cells.AutoFitColumns();//列文本水平居中显示sheet.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;package.Save();//保存}}/// <summary>/// DataTable 保存为Excel文件/// </summary>public static void Test2(){string strFile = filePathExcel + "\\a2.xlsx";if (!Directory.Exists(filePathExcel)){Directory.CreateDirectory(filePathExcel);}DataTable dt = new DataTable();//创建表dt.Columns.Add("ID", typeof(Int32));//添加列dt.Columns.Add("Name", typeof(String));dt.Columns.Add("Age", typeof(Int32));dt.Rows.Add(new object[] { 1, "张三", 20 });//添加行dt.Rows.Add(new object[] { 2, "李四", 25 });dt.Rows.Add(new object[] { 3, "王五", 30 });ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;using (FileStream fileStream = new FileStream(strFile, FileMode.Create))using (ExcelPackage package = new ExcelPackage(fileStream)){var sheet = package.Workbook.Worksheets.Add("木屋");//添加一个sheetsheet.Cells["A1"].LoadFromDataTable(dt, true);package.Save();//保存}}private void button2_Click(object sender, EventArgs e){Test1();}private void button3_Click(object sender, EventArgs e){Test2();}/// <summary>/// 导出数据库模板/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void button1_Click(object sender, EventArgs e){string strFile = filePathExcel + "\\创健数据库模板.xlsx";if (!Directory.Exists(filePathExcel)){Directory.CreateDirectory(filePathExcel);}ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;using (ExcelPackage package = new ExcelPackage()){ExcelWorksheet xlSheet = package.Workbook.Worksheets.Add("创建表");xlSheet.Cells[1, 1].Value = "表中文名称（中文，示例：部门明细表）";xlSheet.Cells[1, 2].Value = "表英文名称（英文，示例：DepartDetails）";xlSheet.Cells[1, 1].Style.WrapText = true;//自动换行,全局xlSheet.Cells[1, 2].Style.WrapText = true;//自动换行,全局xlSheet.Cells[1, 2, 1, 6].Merge = true;xlSheet.Row(2).Style.Font.Name = "Microsoft YaHei";xlSheet.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;xlSheet.Row(2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;xlSheet.Cells[2, 1].Value = "字段";xlSheet.Cells[2, 2].Value = "说明";xlSheet.Cells[2, 3].Value = "类型（nvarchar(100)）";xlSheet.Cells[2, 4].Value = "是否为空（默认不填写就是为空，1为不为空，2为可以空）";xlSheet.Cells[2, 5].Value = "主键（1.为唯一标识主键，2.不为主键，不填默认不是主键）";xlSheet.Cells[2, 6].Value = "索引（1.为索引，2.不为索引，不填默认不是索引）";xlSheet.Cells[2, 7].Value = "默认值";xlSheet.Column(1).Width = 20;//设置列宽xlSheet.Column(2).Width = 20;//设置列宽xlSheet.Column(3).Width = 30;//设置列宽xlSheet.Column(4).Width = 30;//设置列宽xlSheet.Column(5).Width = 30;//设置列宽xlSheet.Column(6).Width = 30;//设置列宽xlSheet.Column(7).Width = 30;//设置列宽xlSheet.Row(2).Style.Font.Name = "Microsoft YaHei";xlSheet.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;xlSheet.Row(2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;xlSheet.Row(1).Height = 40;//自动调整行高xlSheet.Row(2).Height = 40;//自动调整行高xlSheet.Cells[2, 3].Style.Font.Size = 10;xlSheet.Cells[2, 3].Style.WrapText = true;//自动换行,全局xlSheet.Cells[2, 4].Style.Font.Size = 10;xlSheet.Cells[2, 4].Style.WrapText = true;//自动换行,全局xlSheet.Cells[2, 5].Style.Font.Size = 10;xlSheet.Cells[2, 5].Style.WrapText = true;//自动换行,全局xlSheet.Cells[2, 6].Style.Font.Size = 10;xlSheet.Cells[2, 6].Style.WrapText = true;//自动换行,全局xlSheet.Cells[2, 7].Style.Font.Size = 10;xlSheet.Cells[2, 7].Style.WrapText = true;//自动换行,全局xlSheet.Cells.Style.Font.Name = "Microsoft YaHei";//全局if (Directory.Exists(filePathExcel)){DelectDir(filePathExcel);//导出excel前先清楚文件夹中文件，防止数据量大}package.SaveAs(new FileInfo(strFile));package.Dispose();label2.Text = strFile;}}/// <summary>/// 删除指定文件夹中文件/// </summary>/// <param name="srcPath"></param>public static void DelectDir(string srcPath){try{DirectoryInfo dir = new DirectoryInfo(srcPath);FileSystemInfo[] fileinfo = dir.GetFileSystemInfos(); //返回目录中所有文件和子目录foreach (FileSystemInfo i in fileinfo){if (i is DirectoryInfo) //判断是否文件夹{DirectoryInfo subdir = new DirectoryInfo(i.FullName);subdir.Delete(true); //删除子目录和文件}else{File.Delete(i.FullName); //删除指定文件}}}catch (Exception e){throw;}}}
}
