好久没更新博客了！发一个实用的Excel工具类，用来将实体类集合写入到Excel,并且可以设定Excel中列规则，包括是否可以编辑，是否允许重复值。下面是完整代码，主函数里内容为使用说明。DataObject是演示用的一个测试实体类，ExcelWriter和PropertyInfo是主要的类。别忘了加上对Excel程序集的引用
Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Web;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
ExcelWriter writer = new ExcelWriter(@"D:\bbb.xls");
IList<DataObject> objList = new List<DataObject>()
{
new DataObject{P1="1",P2="2",P3="3"},
new DataObject{P1="a1",P2="a2",P3="a3"},
new DataObject{P1="b1",P2="b2",P3="b3"},
new DataObject{P1="c1",P2="c2",P3="c3"},
new DataObject{P1="d1",P2="d2",P3="d3"}
};
IList<PropertyInfo> propertyInfoList = new List<PropertyInfo>()
{
new PropertyInfo{HeadText="一",Name="P1",IsLocked=true,IsUnique=true},
new PropertyInfo{HeadText="二",Name="P2",IsLocked=false,IsUnique=true},
new PropertyInfo{HeadText="三",Name="P3",IsLocked=false,IsUnique=false}
};
writer.WriteDataToExcel<DataObject>(objList, propertyInfoList,null);
}
}
public class DataObject
{
public string P1 { get; set; }
public string P2 { get; set; }
public string P3 { get; set; }
}
public class ExcelWriter
{
private Excel.Application application;
private Excel.Workbook workbook;
private Excel.Worksheet worksheet;
private object missing = Type.Missing;
private string filePath="";
public ExcelWriter(string filePath)
{
application = new Excel.Application();
workbook = (Excel.Workbook)application.Workbooks.Add(missing);
worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
this.filePath = filePath;
}
/// <summary>
/// 将平面二维数组批量写入Excel
/// </summary>
/// <param name="table"></param>
private void WriteData(string[,] table)
{
Excel.Range tableRange = worksheet.get_Range("A1", "A1");
tableRange = tableRange.get_Resize(table.GetLength(0), table.GetLength(1));
tableRange.Value2 = table;
}
/// <summary>
/// 设定列规则，包括不能重复和不能编辑
/// </summary>
/// <param name="propertyInfo"></param>
/// <param name="columnIndex"></param>
private void SetColumnRules(PropertyInfo propertyInfo,int columnIndex)
{
Range columnRange= worksheet.Columns.get_Item(columnIndex + 1, missing) as Range;
//设定锁定规则
columnRange.Locked = propertyInfo.IsLocked;
//设定唯一性规则
if (propertyInfo.IsUnique == true)
{
Range firstCell = columnRange.Cells[1, 1] as Range;
string columnAddress = columnRange.get_Address(missing, missing, XlReferenceStyle.xlA1, missing, missing);
string firstCellAddress = firstCell.get_Address(missing, missing, XlReferenceStyle.xlA1, missing, missing);
string formula = "=COUNTIF(" + columnAddress + "," + firstCellAddress + ")<=1";
formula = formula.Replace("$", "");
columnRange.Validation.
Add(XlDVType.xlValidateCustom, XlDVAlertStyle.xlValidAlertStop, missing, formula, missing);
}
}
/// <summary>
/// 给文件增加一个不能从用户界面查看的隐藏Sheet,并将文件批号写入该隐藏Sheet的第一个单元格
/// 将来可根据文件批号确定Excel文件数据是否可以回读到数据库
/// </summary>
/// <param name="sn"></param>
private void SetFileSN(string sn)
{
Worksheet hiddenSheet = workbook.Sheets.Add(worksheet, missing, missing, missing) as Worksheet;
hiddenSheet.Name = "HiddenSheet";
hiddenSheet.get_Range("A1", "A1").Value2 = sn;
hiddenSheet.Visible = XlSheetVisibility.xlSheetHidden;
}
/// <summary>
/// 保存并关闭文件
/// </summary>
private void CloseExcel()
{
workbook.SaveAs(filePath, missing, missing,
missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
workbook.Close(missing,missing,missing);
application.Quit();
KillExcelProcess(application);
}
/// <summary>
/// 用来关闭打开的Excel进程
/// </summary>
/// <param name="application"></param>
private static void KillExcelProcess(Excel.Application application)
{
IntPtr t = new IntPtr(application.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
[DllImport("User32.dll", CharSet = CharSet.Auto)]
private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// 将对象列表写到Excel中，并根据制定的属性和属性规则确定要写入的数据列和列规则
/// </summary>
/// <typeparam name="T">对象的类型</typeparam>
/// <param name="objList">对象列表</param>
/// <param name="propertyInfoList">要写入的属性和列规则</param>
/// <param name="fileSN">生成文件的序列号，将来从Excel回读数据时来确定文件的合法性</param>
public void WriteDataToExcel<T>
(IList<T> objList,IList<PropertyInfo> propertyInfoList,string fileSN)
{
int columnCout = propertyInfoList.Count;
int rowCount = objList.Count + 1;
string[,] table = new string[rowCount, columnCout];
//从对象列表收集数据到table
for (int columnIndex = 0; columnIndex < columnCout; columnIndex++)
{
//获取列信息
PropertyInfo propertyInfo = propertyInfoList[columnIndex];
//设定列规则
SetColumnRules(propertyInfo, columnIndex);
//设定列标题
table[0, columnIndex] = propertyInfo.HeadText;
//设定该列内容单元格
for (int rowIndex = 1; rowIndex < rowCount; rowIndex++)
{
//由于内容是从第二行开始所以下标减一
T rowObj = objList[rowIndex - 1];
table[rowIndex, columnIndex] =
rowObj.GetType().GetProperty(propertyInfo.Name).GetValue(rowObj, null).ToString();
}
}
//写入数据到Excel
WriteData(table);
//使列规则生效
worksheet.Protect("hanjie", true, true,
missing, missing, missing, missing,
missing, missing, missing, missing,
missing, missing, missing, missing, missing);
//设定文件批号
if (fileSN != null)
{
SetFileSN(fileSN);
}
//关闭文件
CloseExcel();
}
}
/// <summary>
/// 用来存取要在Excel中显示的列的信息，包括显示的标题和对应对象的属性，
/// 和列的规则，是否可以编辑，是否可以有重复值
/// </summary>
public class PropertyInfo
{
public string HeadText {get;set;}
public string Name { get; set; }
public bool IsLocked { get; set; }
public bool IsUnique { get; set; }
}
}
再补充上ExcelReader将数据从Excel读取到DataTable中，并可以检查ExcelWriter中写入的文件批号 保证文件的合法性
Code
public class ExcelReader
{
private Excel.Application application;
private Excel.Workbook workbook;
private Excel.Worksheet worksheet;
private object missing = Type.Missing;
private string filePath = "";
public ExcelReader(string filePath)
{
this.filePath = filePath;
}
/// <summary>
/// 验证文件隐藏Sheet中保存的文件批号是否和要求的一致
/// </summary>
/// <param name="rightSN"></param> private void ValidateFileSN(string rightSN)
{
//读取隐藏Sheet中的文件批号 application = new Excel.Application();
workbook = application.Workbooks.Open(filePath, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
worksheet = workbook.Sheets.get_Item("HiddenSheet") as Excel.Worksheet;
Excel.Range firstRange = worksheet.get_Range("A1", "A1");
string fileSN = firstRange.get_Value(missing).ToString();
//退出并关闭Excel workbook.Close(missing, missing, missing);
application.Quit();
ExcelWriter.KillExcelProcess(application);
//验证批号 if (fileSN != rightSN)
{
throw new Exception("文件批号不正确！");
}
}
/// <summary>
/// 从Excel中读取数据到DataTable如果rightSN为null则不检查文件批号
/// </summary>
/// <param name="rightSN"></param>
/// <returns></returns> public DataTable ReadDataFromExcel(string rightSN)
{
//验证文件批号 if (rightSN != null)
{
ValidateFileSN(rightSN);
}
//读取Excel数据到DataTable string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
OleDbConnection conn = new OleDbConnection(strConn);
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = new DataSet();
strExcel = "select * from [sheet1$]";
try
{
conn.Open();
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, "dtSource");
return ds.Tables[0];
}
catch
{
return null;
}
finally
{
conn.Close();
conn.Dispose();
}
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
