SCPPO（三十一）:解析Excel中的那些事儿
来源：互联网 发布：分层聚类算法 编辑：程序博客网 时间：2024/05/21 07:11
【前言】
由于总部的系统做的不错，公司准备在下面的各个企业推系统；在所有的企业中上海是最强的，公司决定先将上海这个市场拿下；因此我们有了一次感受江南之城的机会，我们组开发人员全员出动配合需求人员利用一个月的时间开发出产品的成型版；我主要是负责系统中解析Excel相关功能，因此有机会学习到Excel解析的相关知识，在此与大家共享。
【解析之路】
一、概述：
1、需求人员和企业相关部门收集起过去一个月的数据，并将数据分门别类汇总到相关的Excel中；
2、对汇总好的Excel进行分析，找出相关的规则；
3、相关实现：根据分析好的规则，来用程序解析Excel表，读取其中的数据放入相应的数据库表中；
4、意义：为其他功能提供数据源进行计算及相关展示。
二、需求：
1、Excel数据展示（由于内容涉及到公司机密，此表格是自己做的Demo）：
2、到数据库表里的效果：
三、相关流程：
1、整体流程：
（1）用户选中文件进行上传
（2）文件上传至服务器的指定目录
（3）从服务器指定位置读取到文件
（4）进行解析
（5）解析入库
2、解析逻辑：
（1）经需求分析Excel中的大类和小类名字是固定的，但是在一张表中可有可无，存到数据库中对应数据库的一个字段；
（2）遇到空在数据库中不显示即可；
四、相关代码实现：
1、去服务器上读出路径并调用解析方法：
#region 将上传完的Excel XX表解析并入库-zhanghan-2016年11月28日 #region 1.解析出服务器上传的Excel名称(包括路径及Excel名称)-zhanghan-2016年11月28日 var PathFileName = System.Web.HttpContext.Current.Server.MapPath(reportPath); #endregion #region 2.解析XX Excel表并将其入库-2016年11月25日 var xxExcelToSql = new ExcelToSQL(); xxExcelToSql.XXExcelToSQL(PathFileName, int.Parse(month.Replace("-", ""))); #endregion #endregion2、辅助解析方法：
（1）解析Excel的工具类用到方法：
using System;using System.Web;using System.Collections;using System.Collections.Generic;using System.Collections.Specialized;using System.Linq;using System.Text;using System.Threading.Tasks;using Microsoft.Office.Interop.Excel;using System.Reflection;using System.Runtime.InteropServices;using System.Diagnostics;using System.Text.RegularExpressions;namespace Apps.Common.ExcelHelper{ public class ExcelCF { /* * 操作excel时需要注意： * 1.坐标的确定，row是什么，cloums是什么 * 2.坐标的开始是从1，1开始 */ private object mValue = System.Reflection.Missing.Value; public Application app; public Workbooks wbs; public Workbook wb; //public Worksheet ws; public ExcelCF() { } public ExcelCF(string path) { GetSheetFrom(path); } public Workbook getwb(string path) { if (app == null) app = new Application(); app.AskToUpdateLinks = false; app.DisplayAlerts = false; wbs = app.Workbooks; wb = wbs.Add(path); return wb; }} （2）批量插入用到SqlBulk:using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;namespace Apps.Common{ public class SqlBulk { /// <summary> /// 批量插入大数据 /// </summary> /// <param name="connectionString">目标连接字符</param> /// <param name="TableName">目标表</param> /// <param name="dt">源数据</param> public static void SqlBulkCopyByDatatable(string TableName, DataTable dt) { string connectionString = ConfigPara.QuartzDBConnection; using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)) { try { sqlbulkcopy.DestinationTableName = TableName; for (int i = 0; i < dt.Columns.Count; i++) { sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); } sqlbulkcopy.WriteToServer(dt); } catch (System.Exception ex) { throw ex; } } } } }}3、解析方法：
using System;using System.Collections.Generic;using System.IO;using System.Linq;using System.Text;using System.Threading.Tasks;using Apps.Common.ExcelHelper;using Microsoft.Office.Interop.Excel;using Apps.Models.Sys;using Apps.Models;using Apps.Models.SH;using System.Data;using System.Data.SqlClient;using System.Threading.Tasks;using Newtonsoft.Json.Linq;using Apps.Common;using Newtonsoft.Json;using Newtonsoft.Json.Linq;using Apps.SH.IBLL;using Microsoft.Practices.XXy;using OfficeOpenXml;namespace Apps.Core{ /// <summary> /// 解析Excel中内容并将其入库-zhanghan-2016年11月25日 /// </summary> public class ExcelToSQL { //错误集合 ValidationErrors validationErrors = new ValidationErrors(); #region 解析XX Excel表并将其存入到数据库中-zhanghan-2016年11月25日 /// <summary> /// 解析XX Excel表并将其存入到数据库中-zhanghan-2016年11月25日 /// </summary> /// <param name="PathFileName">Excel在服务器的名称（路径+名字）</param> /// <param name="reportType">报表类型</param> /// <param name="DetailList">此处用来判断Excel表中有哪些需要解析入库（即不在YY表中的数据不用解析入库）</param> /// <param name="lxDetailList">类型集合,用来解析评估类型时用</param> /// <param name="month">导入的月份,从界面中获取</param> /// <param name="detailBll">需要入库时调用BLL（由于在Apps.Core解决方案下,无法直接获取到BLL,需在CostController调用时传入）</param> /// <returns>是否解析和入库成功</returns> public bool XXExcelToSQL(string PathFileName, string reportType, List<DetailModel> detailList, List<LXDetailModel> lxDetailList, int month, ISH_D_detailBll detailBll) { #region 一.上传到服务器上进行解析入库-zhanghan-2016年11月25日 //读取到Excel ExcelCF cf = new ExcelCF(); Workbook wb = cf.getwb(PathFileName); List<SH_D_XXDetail> xx_DetailList = new List<SH_D_XXDetail>(); #endregion #region 二.如果SH_D_XXDetail中不为空并且wb不为空则去进行解析Excel-2016年11月25日 if (detailList.Count > 0 && wb.Sheets.Count > 0) { //循环判断读取Excel表中的XX是否在SH_D_XXDetail中 for (int i = 0; i < wb.Sheets.Count; i++) { string w1 = wb.Worksheets[i + 1].name; var queryXXDetail = from xx_DetailSingle in detailList where (xx_DetailSingle.XXCode == w1) select new { xx_DetailSingle.XXName }; //如果存在则读取XX名称拼接XX成本(SH_D_XXDetail)Model foreach (var inXXDetail in queryXXDetail) { string XXName = inXXDetail.XXName; //解析到Excel中的数据进行拼接Model int itemX = 6; int itemY = 1; int rowcount = cf.GetXByWID("PP费用",i+1); int colcount = 15; object[,] obj = (object[,])cf.GetCellsValue(itemX, itemY, rowcount, colcount, i + 1); string className = null; string subClassName = null; for (int sheetrow = 0; sheetrow < obj.GetLength(0); sheetrow++) { SH_D_XXDetail XXInoutDetailModel = new SH_D_XXDetail(); int isClass = 0; if (obj[sheetrow + 1, 1] == null)//读到的是空 { className = null; subClassName = null; } else { if (obj[sheetrow + 1, 1].ToString().Contains("|"))//读到是物料 { string mtDetail = obj[sheetrow + 1, 1].ToString(); //截取代码 XXInoutDetailModel.Mt_Code = mtDetail.Split('|')[0]; //截名称 XXInoutDetailModel.Mt_Name = mtDetail.Split('|')[2]; //截取XX编码 XXInoutDetailModel.XXCode = w1; //截取XX名称 XXInoutDetailModel.XXName = XXName; //截取类型编码 string plCode = mtDetail.Split('|')[1]; XXInoutDetailModel.pl_Code = plCode; //截取类型名称 var queryplDetail = from plDetailSingle in lxDetailList where (plDetailSingle.pl_Code == plCode) select new { plDetailSingle.pl_Name }; foreach (var inplDetail in queryplDetail) { XXInoutDetailModel.pl_Name = inplDetail.pl_Name; } //截取大类名称 XXInoutDetailModel.ClassName = className; //截取小类名称 XXInoutDetailModel.SubClassName = subClassName; //截取单位名称 XXInoutDetailModel.XX = mtDetail.Split('|')[3]; //截取月份 XXInoutDetailModel.month = month; //截取本月数量 if (null != obj[sheetrow + 1, 6] && " " != obj[sheetrow + 1, 6]) { float dTemp6; float.TryParse(obj[sheetrow + 1, 6].ToString(), out dTemp6); XXInoutDetailModel.monthamount = dTemp6; } else { XXInoutDetailModel.monthamount = 0; } //截取本月单价 if (null != obj[sheetrow + 1, 7] && " " != obj[sheetrow + 1, 7]) { float dTemp7; float.TryParse(obj[sheetrow + 1, 7].ToString(), out dTemp7); XXInoutDetailModel.monthprice = dTemp7; } else { XXInoutDetailModel.monthprice = 0; } //截取本月金额 if (null != obj[sheetrow + 1, 8] && " " != obj[sheetrow + 1, 8]) { float dTemp8; float.TryParse(obj[sheetrow + 1, 8].ToString(), out dTemp8); XXInoutDetailModel.monthmoney = dTemp8; } else { XXInoutDetailModel.monthmoney = 0; } //截取本月单耗 if (null != obj[sheetrow + 1, 9] && " " != obj[sheetrow + 1, 9]) { float dTemp9; float.TryParse(obj[sheetrow + 1, 9].ToString(), out dTemp9); XXInoutDetailModel.monthXXamount = dTemp9; } else { XXInoutDetailModel.monthXXamount = 0; } //截取本月单位 if (null != obj[sheetrow + 1, 10] && " " != obj[sheetrow + 1, 10]) { float dTemp10; float.TryParse(obj[sheetrow + 1, 10].ToString(), out dTemp10); XXInoutDetailModel.monthXXmoney = dTemp10; } else { XXInoutDetailModel.monthXXmoney = 0; } //截取累计数量 if (null != obj[sheetrow + 1, 11] && " " != obj[sheetrow + 1, 11]) { float dTemp11; float.TryParse(obj[sheetrow + 1, 11].ToString(), out dTemp11); XXInoutDetailModel.sumamount = dTemp11; } else { XXInoutDetailModel.sumamount = 0; } //截取累计单价 if (null != obj[sheetrow + 1, 12] && " " != obj[sheetrow + 1, 12]) { float dTemp12; float.TryParse(obj[sheetrow + 1, 12].ToString(), out dTemp12); XXInoutDetailModel.sumprice = dTemp12; } else { XXInoutDetailModel.sumprice = 0; } //截取累计金额 if (null != obj[sheetrow + 1, 13] && " " != obj[sheetrow + 1, 13]) { float dTemp13; float.TryParse(obj[sheetrow + 1, 13].ToString(), out dTemp13); XXInoutDetailModel.summoney = dTemp13; } else { XXInoutDetailModel.summoney = 0; } //截取累计单耗 if (null != obj[sheetrow + 1, 14] && " " != obj[sheetrow + 1, 14]) { float dTemp14; float.TryParse(obj[sheetrow + 1, 14].ToString(), out dTemp14); XXInoutDetailModel.sumXXamount = dTemp14; } else { XXInoutDetailModel.sumXXamount = 0; } //截取累计单位 if (null != obj[sheetrow + 1, 15] && " " != obj[sheetrow + 1, 15]) { float dTemp15; float.TryParse(obj[sheetrow + 1, 15].ToString(), out dTemp15); XXInoutDetailModel.sumXXmoney = dTemp15; } else { XXInoutDetailModel.sumXXmoney = 0; } //报表类型 XXInoutDetailModel.reportType = reportType; //将解析出的Model添加到List中,该List用来放到数据库中 xx_DetailList.Add(XXInoutDetailModel); } else if (obj[sheetrow + 1, 1].ToString().Contains("一、AAA"))//读取第一个单元格 { isClass = 1; className = "AAA"; XXInoutDetailModel = excelToModel(obj, sheetrow, w1, XXName, lxDetailList, className, subClassName, month, reportType, isClass); //将解析出的Model添加到List中,该List用来放到数据库中 xx_DetailList.Add(XXInoutDetailModel); } else if (obj[sheetrow + 1, 1].ToString().Contains("二、BBB")) { isClass = 1; className = "BBB"; XXInoutDetailModel = excelToModel(obj, sheetrow, w1, XXName, lxDetailList, className, subClassName, month, reportType, isClass); //将解析出的Model添加到List中,该List用来放到数据库中 xx_DetailList.Add(XXInoutDetailModel); } //2.读到的是特殊的大类（特殊情况） else if (obj[sheetrow + 1, 1].ToString().Contains("三、薪酬")) { isClass = 1; className = "薪酬"; XXInoutDetailModel = excelToModel(obj, sheetrow, w1, XXName, lxDetailList, className, subClassName, month, reportType, isClass); //将解析出的Model添加到List中,该List用来放到数据库中 xx_DetailList.Add(XXInoutDetailModel); } else if (obj[sheetrow + 1, 1].ToString().Contains("PP费用")) { isClass = 1; className = "PP费用"; XXInoutDetailModel = excelToModel(obj, sheetrow, w1, XXName, lxDetailList, className, subClassName, month, reportType, isClass); //将解析出的Model添加到List中,该List用来放到数据库中 xx_DetailList.Add(XXInoutDetailModel); } //二.读到是小类 else if (obj[sheetrow + 1, 1].ToString().Contains("bbb")) { isClass = 2; subClassName = "bbb"; XXInoutDetailModel = excelToModel(obj, sheetrow, w1, XXName, lxDetailList, className, subClassName, month, reportType, isClass); //将解析出的Model添加到List中,该List用来放到数据库中 xx_DetailList.Add(XXInoutDetailModel); } else if (obj[sheetrow + 1, 1].ToString().Contains("ccc")) { isClass = 2; subClassName = "ccc"; XXInoutDetailModel = excelToModel(obj, sheetrow, w1, XXName, lxDetailList, className, subClassName, month, reportType, isClass); //将解析出的Model添加到List中,该List用来放到数据库中 xx_DetailList.Add(XXInoutDetailModel); } else //读到是特殊情况 { isClass = 0; XXInoutDetailModel=excelToModel(obj,sheetrow,w1,XXName, lxDetailList,className,subClassName,month,reportType,isClass); //将解析出的Model添加到List中,该List用来放到数据库中 xx_DetailList.Add(XXInoutDetailModel); } } } } } } cf.Close(); #endregion #region 三.将从Excel表中解析到的XX成本明细通过SQLBuk放到数据库XXInoutDetail表中-zhanghan-2016年11月25日 #region 1.校验数据库XXInoutDetail表中是否有该月该类型的数据，若有则清空-zhanghan-2016年11月25日 List<SH_D_XXDetailModel> isHaveDataXXInoutDetail = detailBll.GetListByMoRepType(month, reportType); // bool isDelSuccess = detailBll.DelByMoRepType(ref validationErrors, 1); #region 循环删除-注释-2016年11月25日 if (isHaveDataXXInoutDetail.Count > 0) //若有则先删除 { for (int i = 0; i < isHaveDataXXInoutDetail.Count; i++) { detailBll.DelByMoRepType(ref validationErrors, isHaveDataXXInoutDetail[i].ID); } } #endregion #endregion #region 2.将解析内容放到数据库表XXInoutDetail中-zhanghan-2016年11月25日 if (0 != xx_DetailList.Count) { JArray jObjects = new JArray(); foreach (var item in xx_DetailList) { var jo = new JObject(); jo.Add("Mt_Code", item.Mt_Code); jo.Add("Mt_Name", item.Mt_Name); jo.Add("XXCode", item.XXCode); jo.Add("XXName", item.XXName); jo.Add("pl_Code", item.pl_Code); jo.Add("pl_Name", item.pl_Name); jo.Add("ClassName", item.ClassName); jo.Add("SubClassName", item.SubClassName); jo.Add("XX", item.XX); jo.Add("month", item.month); jo.Add("monthamount", item.monthamount); jo.Add("monthprice", item.monthprice); jo.Add("monthmoney", item.monthmoney); jo.Add("monthXXamount", item.monthXXamount); jo.Add("monthXXmoney", item.monthXXmoney); jo.Add("sumamount", item.sumamount); jo.Add("sumprice", item.sumprice); jo.Add("summoney", item.summoney); jo.Add("sumXXamount", item.sumXXamount); jo.Add("sumXXmoney", item.sumXXmoney); jo.Add("reportType", item.reportType); jObjects.Add(jo); } var dt = JsonConvert.DeserializeObject<System.Data.DataTable>(jObjects.ToString()); SqlBulk.SqlBulkCopyByDatatable("SH_D_XXDetail", dt); } #endregion #endregion return true; } #endregion #region XX成本Excel表中特殊情况（除物料的数据）赋值给Model-zhanghan-2016年11月30日 /// <summary> /// XX成本Excel表中特殊情况（除物料的数据）赋值给Model-zhanghan-2016年11月30日 /// </summary> /// <param name="obj">读取整个Excel的数据</param> /// <param name="sheetrow">读取sheet的行数</param> /// <param name="w1">哪个worksheet</param> /// <param name="XXName">XX名称</param> /// <param name="lxDetailList">评估类型集合</param> /// <param name="className">大类名称</param> /// <param name="subClassName">小类名称</param> /// <param name="month">月份</param> /// <param name="reportType">报表类型</param> /// <param name="isClass">是否是大类或小类--0.表示既不是大类也不是小类;1表示是大类;2.表示是大类.若是大类或小类则将物料名称赋值为类别名称</param> /// <returns>返回XX成本表的Model</returns> private SH_D_XXDetail excelToModel(object[,] obj, int sheetrow, string w1, string XXName, List<LXDetailModel> lxDetailList, string className, string subClassName, int month, string reportType, int isClass) { SH_D_XXDetail XXInoutDetailModel = new SH_D_XXDetail(); //截取物料代码 XXInoutDetailModel.Mt_Code = null; //获取名称---0.若是读取其他类型则正常读取名称;1.若是读取大类一行则将名称赋值为大类的名称;2.若是小类则将名称赋值为小类的名称. string MtName = null; if (null != obj[sheetrow + 1, 2] && " " != obj[sheetrow + 1, 2]) { MtName=obj[sheetrow + 1, 2].ToString(); } switch(isClass){ case 1: MtName=className; break; case 2: MtName=subClassName; break; } XXInoutDetailModel.Mt_Name = MtName; //截取XX编码 XXInoutDetailModel.XXCode = w1; //截取XX名称 XXInoutDetailModel.XXName = XXName; //截取类型编码&评估类型名称 string plCodeSpecial = null; string plNameSpecial = null; if (null != obj[sheetrow + 1, 4] && " " != obj[sheetrow + 1, 4]) { plCodeSpecial = obj[sheetrow + 1, 4].ToString(); var queryplDetailSpecial = from plDetailSingleSpecial in lxDetailList where (plDetailSingleSpecial.pl_Code == plCodeSpecial) select new { plDetailSingleSpecial.pl_Name }; foreach (var inplDetailSpecial in queryplDetailSpecial) { plNameSpecial = inplDetailSpecial.pl_Name; } } XXInoutDetailModel.pl_Code = plCodeSpecial; XXInoutDetailModel.pl_Name = plNameSpecial; //截取大类名称 XXInoutDetailModel.ClassName = className; //截取小类名称 XXInoutDetailModel.SubClassName = subClassName; //截取单位名称 string XXTemp = null; if (null != obj[sheetrow + 1, 5] && " " != obj[sheetrow + 1, 5]) { XXTemp = obj[sheetrow + 1, 5].ToString(); } XXInoutDetailModel.XX = XXTemp; //截取月份 XXInoutDetailModel.month = month; //截取本月数量 if (null != obj[sheetrow + 1, 6] && " " != obj[sheetrow + 1, 6]) { float dTemp6; float.TryParse(obj[sheetrow + 1, 6].ToString(), out dTemp6); XXInoutDetailModel.monthamount = dTemp6; } else { XXInoutDetailModel.monthamount = 0; } //报表类型 XXInoutDetailModel.reportType = reportType; return XXInoutDetailModel; } #endregion }}五、C#中解析Excel的方法总结：
1、通过COM组件方式,需要注意一些东西--参考：Excel COM组件使用的注意事项和一些权限问题
2、通过EppPlus方式，参考：使用EPPlus(C#)读写Excel
3、OpenXML（参考：OpenXml读取Excel）
【总结】
1、实现思路很重要，本篇博文最重要是在根据具体的场景分析Excel表的规则；
2、方法总比困难多，在实际实现的时候遇到很多读取不正常，需要耐心的调试，或找其他的方法来弥补；
3、只有想不到没有做不到，之前自己没有接触过解析Excel的工作，原来还可以这样分析数据，打开眼界。
2 0
- SCPPO（三十一）:解析Excel中的那些事儿
- SCPPO（十四）:Windows下Redis安装的那些事儿！
- SCPPO（二十三）:SQLServer数据库备份那些事儿
- JAVA操作Excel那些事儿
- Excel:日期计算那些事儿
- 浅谈面试中的那些事儿！
- 连接Oracle中的那些事儿
- 发布网站中的那些事儿
- 正则表达式中的那些事儿
- angular中的那些事儿---过滤器
- SQL Server于Excel的那些事儿
- Android那些事儿（一）成长中的Android
- 传说中的WCF（2）：服务协定的那些事儿
- 网络中的那些事儿（一）之神奇的通讯
- 网络中的那些事儿（二）之总体规划
- 高校云平台（二）:评教中的那些事儿！
- 软考（二）:网络中的那些事儿
- 网络中的那些事儿（五）之校园网规划综述
- ios消息发送
- apache配置文件说明
- spark-DataFrame学习记录-[1]基础部分
- 搭建Android系统C程序调试环境
- 小白版C++八皇后回溯
- SCPPO（三十一）:解析Excel中的那些事儿
- 第5章 硕正WEB组件的应用
- 单例模式
- Android框架之图片框架Glide
- 微信开发资源下载
- MySQL下载安装、配置与使用（win7x64）
- 面向对象六大原则
- 第四章 使用判断语句
- 第6章 工作流的应用实例
