最近项目中做了基本所有对EXCEL的操作，做个笔记，方便以后使用。
一：先来个导入EXCEL的
/// <summary>
/// Excel转Sql方法
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="sheetName">单元簿名称</param>
/// <returns></returns>
public static DataTable ExcelDataTable(string filePath, string sheetName)
{
DataTable dt = null;
//HDR=Yes:第一行是列名
//IMEX=1:把数据作为text 类型
string conStr = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';", filePath);
string strSql = string.Format("select * from [{0}$]", sheetName);
OleDbConnection con = new OleDbConnection(conStr);
OleDbDataAdapter oda = new OleDbDataAdapter(strSql, conStr);
try
{
dt = new DataTable();
oda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
oda.Dispose();
con.Close();
con.Dispose();
}
}
}
View Code
上面是封装的Common层的工具类，方便将Excel转换为DataTable.
下面上调用的代码
string conString = "server=192.168.1.44;database=DataTest;uid=sa;pwd=sa";
//调用Excel转Sql方法
DataTable dt = OutExcel.ExcelDataTable(imageUrl, "sheet1");
//创建批量DataTable导入Sql
using (SqlBulkCopy copy = new SqlBulkCopy(conString, SqlBulkCopyOptions.UseInternalTransaction))
{
//指定数据库表名
copy.DestinationTableName = "BaseTags";
//写入Sql
copy.WriteToServer(dt);
}
View Code
其中 conString为要插入的数据库配置，再指定表明，就可以插入数据了，插入的方式为追加在数据表中。
二：再来个导出Excel的
public class Util<T>
{
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="response">返回类型</param>
/// <param name="list">要导出的数据</param>
/// <param name="columnName">列名称</param>
/// <param name="propertyName">属性名称</param>
/// <param name="ExcelTitle">Excel标题</param>
/// <param name="strUserMsg"></param>
public static void DataBindTitleExcel(System.Web.HttpResponseBase response, List<T> list, List<string> columnName, List<string> propertyName, string ExcelTitle, string strUserMsg)
{
if (list.Count == 0 || list == null)
{
response.Write("<script>alert('对不起,没有查询到任何记录,导出失败!')</script>");
response.End();
}
response.ContentEncoding = Encoding.GetEncoding("GB2312");
response.ContentType = "application/ms-excel";
response.AppendHeader("Content-Disposition", "attachment;filename=Export.xls");
int count = list.Count;
StringBuilder builder = new StringBuilder();
builder.Append("<html><head>\n");
builder.Append("<meta http-equiv=\"Content-Language\" content=\"zh-cn\">\n");
builder.Append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">\n");
builder.Append("</head>\n");
builder.Append("<table border=1>");
if (ExcelTitle != "")
{
string str = "<font size=4><b>" + ExcelTitle + "</b></font>";
if (strUserMsg != "")
{
str = str + "（" + strUserMsg + "）";
}
builder.Append(string.Concat(new object[] { "<tr><td colspan=", count, ">", str, "</td></tr>" }));
}
builder.Append("<tr><td colspan=" + count + " valign=middle height=24>");
builder.Append("查询时间：" + DateTime.Now.ToString("G") + "</td></tr>");
builder.Append("<tr>\n");
for (int i = 0; i < columnName.Count; i++)
{
builder.Append("<td bgcolor=#969696><b>" + columnName[i] + "</b></td>\n");
}
Type objType = typeof(T);
BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识
PropertyInfo[] propInfoArr = objType.GetProperties(bf);
foreach (T model in list)
{
builder.Append("<tr>");
foreach (PropertyInfo propInfo in propInfoArr)
{
foreach (string propName in propertyName)
{
if (string.Compare(propInfo.Name.ToUpper(), propName.ToUpper()) == 0)
{
PropertyInfo modelProperty = model.GetType().GetProperty(propName);
if (modelProperty != null)
{
object objResult = modelProperty.GetValue(model, null);
builder.Append("<td style='vnd.ms-excel.numberformat:@'>" + ((objResult == null) ? string.Empty : objResult) + "</td>");
}
else
{
throw new Exception("Property name may be not exists!");
}
}
}
}
builder.Append("</tr>\n");
}
builder.Append("</table>\n");
response.Write(builder.ToString());
response.End();
}
}
View Code
我调用时候写的
Util<BaseTags>.DataBindTitleExcel(Response, listbt, columnName, propertyName, "Test系统数据管理", "");
其中第一个参数MVC项目里为Response，ashx里为context.response,第二个参数为你要导出的数据列表，一般为泛型LIST类型。第三个参数columnName为Excel里的每一列的表头，第四个参数propertyName为对应数据库的列，第五个参数为导出的Excel标题，第六个参数为操作人员。
PS：附上一个DEMO做参考。
public ActionResult ToExcel(int id)
{
List<BaseTags> listbt = db.Set<BaseTags>().Where<BaseTags>(x => x.CollectorId == id).ToList();
//这里是EXCEL里的内容
List<string> columnName = new List<string>();
columnName.Add("Tagname");
columnName.Add("ExtendId");
columnName.Add("Description");
columnName.Add("Comment");
columnName.Add("EngUnits");
columnName.Add("DataType");
columnName.Add("CollectorId");
columnName.Add("GroupNumber");
columnName.Add("SourceAddress");
columnName.Add("CollectionInterval");
columnName.Add("HiEngUnits");
columnName.Add("LoEngUnits");
columnName.Add("Expression");
columnName.Add("IsSaveHistorian");
columnName.Add("IsArchive");
//这是是对应数据库的列
List<string> propertyName = new List<string>();
propertyName.Add("Tagname");
propertyName.Add("ExtendId");
propertyName.Add("Description");
propertyName.Add("Comment");
propertyName.Add("EngUnits");
propertyName.Add("DataType");
propertyName.Add("CollectorId");
propertyName.Add("GroupNumber");
propertyName.Add("SourceAddress");
propertyName.Add("CollectionInterval");
propertyName.Add("HiEngUnits");
propertyName.Add("LoEngUnits");
propertyName.Add("Expression");
propertyName.Add("IsSaveHistorian");
propertyName.Add("IsArchive");
Util<BaseTags>.DataBindTitleExcel(Response, listbt, columnName, propertyName, "Test系统数据管理", "");
return View();
}
View Code
三：对Excel的操作（NPOI）
使用NPOI可以百度搜索NPOI直接Down最新版本，最好全部引用，缺少引用将会导入无法使用某些类
其中
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
StringBuilder sb = new StringBuilder();
using (Stream stream = File.OpenRead(PATH))
{
XSSFWorkbook workbook = new XSSFWorkbook(stream);
//j为行数
int j = workbook.GetSheetAt(1).LastRowNum;
//固定内容
sb.Append(@"MEPMD01,19970819,Schneider Electric,,,""SSTJEC\Sino Singapore TianJin Eco City|TianJin_Eco_City\TianJin Eco City"",");
string UtcNow = changeDateTimeFormat(DateTime.UtcNow);
//时间为文件上传FTP时间（UTC时间）
sb.Append(UtcNow);
sb.Append(',');
//用“EO系统名称”一行的点名替代
sb.Append(workbook.GetSheetAt(1).GetRow(4).GetCell(1).StringCellValue);
sb.Append('|');
//用点表里“地址”一行相应内容替代
sb.Append(workbook.GetSheetAt(1).GetRow(5).GetCell(1).NumericCellValue);
sb.Append(',');
//如果数据在合理的范围内，即为有效数据时此处填写OK
sb.Append("OK");
sb.Append(',');
//点表中“点类型”一行相应内容替代
sb.Append(workbook.GetSheetAt(1).GetRow(6).GetCell(1).StringCellValue);
sb.Append(',');
//用点表中“单位”一行内容替代
sb.Append(workbook.GetSheetAt(1).GetRow(3).GetCell(1).StringCellValue);
sb.Append(",1,00000005,3,");
//为后面第一个采样值的采样时间
string firsttime = DateTime.FromOADate(workbook.GetSheetAt(1).GetRow(10).GetCell(0).NumericCellValue).ToString();
string UtcFirst = changeDateTimeFormat(Convert.ToDateTime(firsttime));
sb.Append(UtcFirst);
sb.Append(",,");
//第一个采样值
sb.Append(workbook.GetSheetAt(1).GetRow(8).GetCell(1).NumericCellValue);
sb.Append(",,,");
//第二个采样值
sb.Append(workbook.GetSheetAt(1).GetRow(9).GetCell(1).NumericCellValue);
sb.Append(",,,");
//第三个采样值
sb.Append(workbook.GetSheetAt(1).GetRow(10).GetCell(1).NumericCellValue);
sb.Append(",");
CreateTxt("E:\\" + dirs[intFile].Name.Split('.')[0] + ".cemp", sb.ToString());
}
View Code
其中PATH为文件路径，workbook即为工作薄，GetRow(X)控制第几行（默认从0开始）,GetCell(X)控制第几列（默认从0开始），
Excel中string类型数据采用StringCellValue获取，数值类型采用NumericCellValue获取，Datetime类型直接获取到得是数值，并非时间，要获得时间通过DateTime.FromOADate(workbook.GetSheetAt(1).GetRow(10).GetCell(0).NumericCellValue).ToString();方式获取
转载于:https://www.cnblogs.com/ckblogs/p/4029233.html