【C#工具类】Excel接口（一）

需求描述

具体需求分析

学习交流（Bug待解决）

Debug代码（写代码的过程，逐渐完善的过程）

参考资料

扩展阅读

需求描述

用C#生成两个函数

1. 盲读Excel (Excel文件名)
读取所有单元格的数据，并输出Json
如：{[{"X":"2","Y":"4","V":"第2行第4列的值"}, {"X":"3","Y":"8","V":"第3行第8列的值"}]}

2. 按指定参数读取Excel (Excel文件名，Json参数）

{ "固定单元格读取":[
       {"X":"2","Y":"4","V":"第2行第4列的值"},
{"X":"3","Y":"8","V":"第3行第8列的值"}   ],
"二位表格读取":[
       {"二维表格名称":"表格1","列头行数":"1","X1":"2","X2":"6","Y1":"4","Y2":"","行结束方式":"固定列动态行单元格为具体文本","行结束识别X":"1","行结束文本":""},
       {"二维表格名称":"表格1","列头行数":"1","X1":"2","X2":"6","Y1":"4","Y2":"","行结束方式":"行无数据","行结束识别X":"","行结束文本":""}
   ]
}

"单元格":[
       {"X":"2","Y":"4","V":"第2行第4列的值"},
{"X":"3","Y":"8","V":"第3行第8列的值"}   ],
"表格":[
       {"Name":"表格1",Data:[{"列1名":"1","列2名":"2"},{"列1名":"1","列2名":"2"}]},
       {"Name":"表格1",Data:[{"列1名":"1","列2名":"2"},{"列1名":"1","列2名":"2"}]}
   ]

具体需求分析

Function1.

input:EXCEL（Sheet）

output:内容转换成Json

Function2.

input:固定单元格 or 二位表格（固定单元格 and 二位表格）

output:单元格 and 表格

思路：

1.读取EXCEL

网上有两种方式：

第一种是安装AccessDatabaseEngine，思路：

1)根据Excel文件获取所有Sheet名称

2)获取每一个Sheet的内容组装dataTable

3)table转Json

第二种是利用Npoi读取excel，思路：

1)将excel文件中的内容读取出来，存放到DataSet中

2)将DataTable转换成对应的list对象

3)将list对象转换成json，传递到前端

2.Json转换

1)引用Newtonsoft.Json.dll

下载地址：https://www.newtonsoft.com/json

下载完成后，找到对应版本的dll，项目中直接引用即可。

还有另外一种下载方式，使用Nuget ：打开vs工具 - NuGet程序包管理器 - 程序包管理器控制台，这时在VS的底部窗口出现命令行：pm>install-package newtonsoft.json 执行完，会提示下载在哪个位置，再从项目中引用即可。

2)json和List的转换，更多是考虑List<T>

注：下面记录的是开发过程中遇到的一些情况，然后随手记了一部分核心代码，完整的DEMO贴在最后。

测试案例 (简单设计了两个表格内容)

学习交流（Bug待解决）

Form2

方式一
output==>读文件显示LIST在DGV中，然后用MESSAGEBOX显示Json返回结果。

0.27读出来是.27，但其他带小数的数字没影响。

可优化点1：方式一的返回参数其实就是方式二的【单元格】返回参数，所以这两个返回结果的实体类可以合并。（Demo已优化）

方式二

input==>{"X":"2","Y":"4","V":"第2行第4列的值"},{ "X":"3","Y":"8","V":"第3行第8列的值"}

1)C#默认从0开始，(2,4)其实在系统中对应的是(3,5)并且还需要考虑是否会存在越界

2)需要格式化输入参数，关于最后一个传参V是否赋值存疑（思考：有没有设定非必填参数的方法？存入后台可以设定not null或者null,但是前端传参似乎都是必填的）【通用方法】

输入（2，4）和（2，4，0）和（2，4，Null）是否应该判定为一致？

3） {"二维表格名称":"表格1","列头行数":"1","X1":"2","X2":"6","Y1":"4","Y2":"","行结束方式":"固定列动态行单元格为具体文本","行结束识别X":"1","行结束文本":""}

“列头行数”私以为有三种情况：0-没有表头即默认第一行为列头;1-默认第一行为列头;N-跳过多行，从第N行开始读取EXCEL内容

范围：从X1:Y1至X2:Y2（给的参考数据里Y2默认为空，但三个参数也确实可以确定范围）

行结束方式：行无数据（这个是具体输入的文字还是怎么决定的呢？）

行结束识别X：具体文本中某个字符？比如逗号空格之类

行结束文本：给的参考数据是空，所以具体逻辑不太清楚（在网上有参考了部分资料，但是好像还都没有细化到这个程度）

待完善点：其实主要是因为我对这个Input的理解还不够清晰，所以先默认表名称，开始表头和表格范围都是必填项，后面3个参数待优化补充。（NPOI中其实也都有提供相关属性）

4) 固定单元格 or 二位表格还是固定单元格 and 二位表格 ?

固定单元格 or 二位表格 :二种方式任选其一，固定单元格的格式可以选取多个，并不仅限于两个（List允许上限范围内都可以查询）同时查询时也存在允许输入任一种或者两种（需要检验输入内容是否冲突）或者完全不选任何一种默认全读。

固定单元格 and 二位表格 : 将固定单元格限定为两个，然后作为二位表格中的范围条件（这种思路开发起来更简单，但是从参考Input中并没有发现关联的关系，所以后续可以作为可优化点4）

output==>读表格名称时默认显示的是Table1,Table2这样的，其实私以为返回1和Sheet1这样的更合理一些。（定义的DataTable dt,然后取的dt.TableName;）

==》关于获取到表名1和Sheet 的方式，可以利用NPOI中的方法(类似下面的思路，Demo已优化)

  NPOI.SS.UserModel.ISheet st;var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);var wk=new  new XSSFWorkbook(fs);//2007之后版本的excel//获取读取的Sheet表的索引st = wk.GetSheetAt(0);MessageBox.Show(st.SheetName); //Sheet表名

LIST绑定DGV，因为LIST泛型走了一点弯路，暂时也没想到更好的处理方式（上图是所有的数据）然后绑定DGV的写法疑似存在一定局限性（中间切换的过程是通过MessageBox控制的，然后目前只能单一的绑定List,没法直接绑定实体类）效果如下：

对应代码：

            ExcelToJSON tolist = new ExcelToJSON();List<Result> list = tolist.ImportExcelResult(strFilePath);JavaScriptSerializer serializer = new JavaScriptSerializer();string jsonData = serializer.Serialize(list);MessageBox.Show(jsonData);foreach(Result rs in list){foreach (单元格Item cell in rs.单元格){this.dataGridView1.DataSource = new BindingSource(new BindingList<单元格Item>(rs.单元格), null);//MessageBox.Show("单元格Item");}MessageBox.Show(" 单元格Item => 表格Item.DataItem ");foreach (表格Item sheet in rs.表格){foreach (DataItem dataitem in sheet.Data){this.dataGridView1.DataSource = new BindingSource(new BindingList<DataItem>(sheet.Data), null);//MessageBox.Show("DataItem");}}}

可优化点2：目前两个方式的返回结果是合在一起的返回的，理论上还可以优化精简成泛型。

可优化点3：目前是进入目录选择相应的EXCEL文件，然后回传相关数据。其实还可以只提供目录，由程式自动遍历找到所有的EXCEL文件。（思考：此处的文件过滤也可以做成下拉选项，除EXCEL是否还可以对CSV,TXT,DOC等文件进行不同的操作呢？）

Debug代码（写代码的过程，逐渐完善的过程）

1.DATATABLE转换JSON

思考：读文件显示表头，如何默认过滤第一行标头？

            string strFilePath, strFileName;strFilePath = FilePathHelper.SelectFile(null);strFileName = textBox2.Text;if (string.IsNullOrEmpty(strFileName)){strFileName = " ";}ExcelToJSON ImportExcel = new ExcelToJSON();var excelData = ImportExcel.ImportExcelToDataSet(strFilePath); //遍历DataSetif (excelData.Tables.Count < 1){//...}foreach (DataTable dt in excelData.Tables){//按Sheet读取Table内容（JSON,默认会带上列名）ExcelToJSON tojson = new ExcelToJSON();string rs = tojson.ToJson(dt);MessageBox.Show(rs);}

因为想找 TableName 写的一段带参数名称的输出，然而并没有找到。

            string strFilePath, strFileName;strFilePath = FilePathHelper.SelectFile(null);strFileName = textBox2.Text;if (string.IsNullOrEmpty(strFileName)){strFileName = " ";}ExcelToJSON ImportExcel = new ExcelToJSON();var excelData = ImportExcel.ImportExcelToDataSet(strFilePath); //遍历DataSetif (excelData.Tables.Count < 1){//...}foreach (DataTable dt in excelData.Tables){//按Sheet读取Table内容（会带上格式）DataContractJsonSerializer serializer = new DataContractJsonSerializer(dt.GetType());using (MemoryStream ms = new MemoryStream()){serializer.WriteObject(ms, dt);StringBuilder sb = new StringBuilder();sb.Append(Encoding.UTF8.GetString(ms.ToArray()));MessageBox.Show(sb.ToString());}}

最后在NPOI读取EXCEL时将对应表名赋值给table，最后还是用dt.TableName读到了相应表名。

优化代码： table.TableName = sheet.SheetName; //Sheet表名

2.没有套用INPUT的OUTPUT方式二( 先依照方式一做的output，直接根据选择的文件名返回所有数据)

function2事件：

        private void button2_Click(object sender, EventArgs e){string strFilePath, strFileName;strFilePath = FilePathHelper.SelectFile(null);strFileName = textBox2.Text; //先默认不填if (string.IsNullOrEmpty(strFileName)){strFileName = " ";}ExcelToJSON tolist = new ExcelToJSON(); List<Result> list = tolist.ImportExcelResult(strFilePath);JavaScriptSerializer serializer = new JavaScriptSerializer();string jsonData = serializer.Serialize(list);MessageBox.Show(jsonData);/*foreach(Result rs in list){foreach (单元格Item cell in rs.单元格){this.dataGridView1.DataSource = new BindingSource(new BindingList<单元格Item>(rs.单元格), null);//MessageBox.Show("单元格Item");}MessageBox.Show(" 单元格Item => 表格Item.DataItem ");foreach (表格Item sheet in rs.表格){foreach (DataItem dataitem in sheet.Data){this.dataGridView1.DataSource = new BindingSource(new BindingList<DataItem>(sheet.Data), null);//MessageBox.Show("DataItem");}}}*/}

ExcelToJSON中ImportExcelResult(strFilePath);的代码：

        #region DataTable内容转成Result(只选择文件即可返回两种结果)public List<Result> ImportExcelResult(string filePath){List<Result> result = new List<Result>();var excelData = ImportExcelToDataSet(filePath);                //遍历DataSetif (excelData.Tables.Count < 1){               return result;}foreach (DataTable dt in excelData.Tables){按Sheet读取Table内容（JSON,默认会带上列名）//ExcelToJSON tojson = new ExcelToJSON();//string rs = tojson.ToJson(dt);//MessageBox.Show(rs);按Sheet读取Table内容（会带上格式）//DataContractJsonSerializer serializer = new DataContractJsonSerializer(dt.GetType());//using (MemoryStream ms = new MemoryStream())//{//    serializer.WriteObject(ms, dt);//    StringBuilder sb = new StringBuilder();//    sb.Append(Encoding.UTF8.GetString(ms.ToArray()));//    MessageBox.Show(sb.ToString());//}Result rs = new Result();List<单元格Item> item = new List<单元格Item>();              List<表格Item> sheet = new List<表格Item>();表格Item bit = new 表格Item();bit.Name = dt.TableName;foreach (DataRow dr in dt.Rows){List<DataItem> data = new List<DataItem>();                    List<string> columnName = new List<string>();  //实例化一个参数集合foreach (DataColumn dataColumn in dt.Columns){columnName.Add(dataColumn.ColumnName);}for (var i = 0; i < dr.ItemArray.Length; i++){单元格Item it = new 单元格Item();DataItem di = new DataItem();di.col_X = columnName[i]; //DataItem集合中添加键值//验证是否包含特殊字符if (dr.ItemArray[i].ToString() != "" && ValidateSymbol(dr.ItemArray[i].ToString()) == true){it.X = "0";it.Y = "0";it.V = "0";}else{                        if (dr.ItemArray[i].ToString() != ""){it.X = dt.Rows.IndexOf(dr).ToString();it.Y = i.ToString();it.V = dr.ItemArray[i].ToString();                              }                          }item.Add(it);       rs.单元格 = item;di.col_Y = dr.ItemArray[i].ToString();data.Add(di);}bit.Data=data;}sheet.Add(bit);rs.表格=sheet;result.Add(rs);}        return result;}#endregion

3.转换方式二Input的JSON格式

输入的是实体类，传参时传入实体类，转换json格式放在工具类中还是在输入界面更合适？（textBox.Text->List<CellItem>->Input->json解析后使用）思考：可否写一个通用的转换方法？

将1，3赋值给单元格List

           string inputValue = textBox1.Text.Trim(); //"1,3" inputValue = inputValue.Replace("，",",");//替换中文状态的逗号Input inparam = new Input();//固定单元格List<CellItem> cs = new List<CellItem>();//{"X":"2","Y":"4","V":"第2行第4列的值"},{ "X":"3","Y":"8","V":"第3行第8列的值"}List<string> inputlist = new List<string>(inputValue.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries)); //去除中间的空格CellItem ci = new CellItem();    ci.X = inputlist[0];ci.Y = inputlist[1];//ci.V = "";//Input时要不要赋值,我的理解是输入固定单元格，输出时返回这个单元格所对应的数据cs.Add(ci);inparam.CellItem=cs;

输入参数应该请使用者如何输入？目前先手输数组，类似（1，3），默认参数V不传；但是存在C#从0开始计数，实际习惯从1开始数EXCEL单元格的情况，并且由使用者输入的参数还有可能存在中英文符号，不加括号等等各种格式(仅查一个单元格时肯定大部分人都不喜欢加括号，就算key括号也会存在()<>{}并且全角半角的输入法区别)。思考：可否通过正则或者其他方式单独写一个处理方法让input格式更规范统一？（思考：可否用正则取出了括号中间的单元格数组）

网上有找到过一些正则截取括号中间内容的写法，但是处理起连续的（）好像一直不太得劲，后续有精力再研究一下，目前用了比较暴力的原始截取方法。

            string inputValue = "(1,3,1),(1,4,5)";//string inputValue ="(1,3),(1,4)";// var str=  Regex.Match(inputValue, @"(\([0-9]+,[0-9]+\))");//1,3int length = inputValue.Length - 1;int firstLeft = inputValue.IndexOf("(");int firstRight = inputValue.IndexOf(")")-1;int arr = firstRight - firstLeft;string str2 ;while (length > 0) //10{//结束位置 减 1 再减 开始位置 获取中间位置数str2=inputValue.Substring(inputValue.IndexOf("(") + 1, arr);//参数1：开始位置加1 参数2：长度：中间位置数MessageBox.Show(str2);//这里就是我想要的（）中间的数据inputValue = inputValue.Remove(firstLeft, firstRight+2>length?length:firstRight+2);firstLeft = inputValue.IndexOf("(");firstRight = inputValue.IndexOf(")") - 1;arr = firstRight - firstLeft;length = inputValue.Length - 1;}

转换JSON格式的方法：

        public static string Obj2Json<T>(T data){try{DataContractJsonSerializer serializer = new DataContractJsonSerializer(data.GetType());using (MemoryStream ms = new MemoryStream()){serializer.WriteObject(ms, data);return Encoding.UTF8.GetString(ms.ToArray());}}catch{return null;}}

4.方式二的Input表格读取范围

1）通过固定单元格决定二位表格选取范围（就是上面的可优化点4）

【固定单元格】对应的实体类在本次Project中多次用到，所以顺手就写了这种方式

但是通过上图可知我这里还有一个BUG:

对于没有读取的栏位，比如ABEFG，在单元格读取时在DGV中显示成了空白，并不是我最初设想的直接只返回两行的情况。有试过在读取Table栏位时只读取第C列和第D列，然而二维表格读取的分支里，赋值时会出现找不到对应列的情况。所以最后就变成了上述效果。

参考代码：

string inputValue = textBox1.Text.Trim(); //"1,3" inputValue = inputValue.Replace("，",",");//替换中文状态的逗号//通过遍历inputValue获取具体的单元格//inputValue = inputValue.Replace("（", "").Replace("）","").Replace("(", "").Replace(")", "");//去除所有（），默认中间也会key逗号，遍历每2-3个元素为固定单元格//把List集合转换为json字符串JavaScriptSerializer serializer = new JavaScriptSerializer();Input inparam = new Input();//固定单元格List<CellItem> cs = new List<CellItem>();//"(1,3),(1,4)"int length = inputValue.Length - 1;int firstLeft = inputValue.IndexOf("(");int firstRight = inputValue.IndexOf(")") - 1;int arr = firstRight - firstLeft;string str2;List<string> resList = new List<string>();while (length > 0) //10{//结束位置 减 1 再减 开始位置 获取中间位置数str2 = inputValue.Substring(inputValue.IndexOf("(") + 1, arr);//参数1：开始位置加1 参数2：长度：中间位置数resList.Add(str2);inputValue = inputValue.Remove(firstLeft, firstRight + 2 > length ? length : firstRight + 2);firstLeft = inputValue.IndexOf("(");firstRight = inputValue.IndexOf(")") - 1;arr = firstRight - firstLeft;length = inputValue.Length - 1;}foreach (string i in resList.ToArray()){//{"X":"2","Y":"4"},{ "X":"3","Y":"8"}//{"X":"2","Y":"4","V":"第2行第4列的值"},{ "X":"3","Y":"8","V":"第3行第8列的值"}List<string> cist = new List<string>(i.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries)); //去除中间的空格CellItem ci = new CellItem();ci.X = cist[0];ci.Y = cist[1];//ci.V = "";//Input时要不要赋值,我的理解是输入固定单元格，输出时返回这个单元格所对应的数据cs.Add(ci);}inparam.CellItem=cs;//二位表格//{ "二维表格名称":"表格1","列头行数":"1","X1":"2","X2":"6","Y1":"4","Y2":"","行结束方式":"固定列动态行单元格为具体文本/行无数据","行结束识别X":"1","行结束文本":""},List<SheetItem> ss = new List<SheetItem>();SheetItem si = new SheetItem();si.Sheet = strFileName;//Sheet1si.Col = "1";si.X1 = cs[0].X;si.X2 = cs[1].X;si.Y1 = cs[0].Y;si.Y2 = cs[1].Y;si.Row = "";si.Row_X = "";si.Row_End = "";ss.Add(si);inparam.SheetItem = ss;//格式化传参JSON Inputstring jsonInput = serializer.Serialize(inparam);MessageBox.Show(jsonInput);

public DataSet ImportExcelToDataSet(string filePath,Input inparam){            //表名             inparam.SheetItem[0].Sheet//列头行数         inparam.SheetItem[0].Col//范围            inparam.SheetItem[0].X1/X2/Y1/Y2//行结束方式//行结束识别//行结束文本DataSet ds = new DataSet();IWorkbook workbook;string fileExt = Path.GetExtension(filePath).ToLower();try{using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)){if (fileExt == ".xlsx"){workbook = new XSSFWorkbook(fs);//2007之后版本的excel}else{workbook = new HSSFWorkbook(fs);//2003版本的excel}//for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)//{//获取读取的Sheet表的索引ISheet sheet = workbook.GetSheet(inparam.SheetItem[0].Sheet);DataTable table = new DataTable();table.TableName = sheet.SheetName; //Sheet表名 int firstrow;if (inparam.SheetItem[0].Col == "1" || inparam.SheetItem[0].Col == "0"){//将第一行的文本作为列名 inparam.SheetItem[0].Colfirstrow = sheet.FirstRowNum;}else{firstrow = Convert.ToInt16( inparam.SheetItem[0].Col)-1;}IRow headerRow = sheet.GetRow(firstrow);int cellCount = headerRow.LastCellNum;for (int i = sheet.FirstRowNum; i < cellCount; i++){DataColumn column;object obj = GetValueType(headerRow.GetCell(i));if (obj == null || obj.ToString() == string.Empty){column = new DataColumn("Columns" + i.ToString());}else{column = new DataColumn(obj.ToString());}table.Columns.Add(column);}//读取第一行下面的数据,将他们作为数据行存储//for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)  //首列名称不保留//excel单元格默认+1，所以此处设定-1 int begini = Convert.ToInt32(inparam.SheetItem[0].X1) - 1;int endi = Convert.ToInt32(inparam.SheetItem[0].X2) - 1;int beginj = Convert.ToInt32(inparam.SheetItem[0].Y1) - 1;int endj = Convert.ToInt32(inparam.SheetItem[0].Y2) - 1;for (int i =begini ; i <= endi; i++)  {IRow row = sheet.GetRow(i);if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == ""){// 如果遇到第一个空行，跳出本次循环，继续向下读取                              continue;}DataRow dataRow = table.NewRow();for (int j = beginj; j <= endj; j++){if (row.GetCell(j) != null){dataRow[j] = row.GetCell(j).ToString();}}table.Rows.Add(dataRow);}ds.Tables.Add(table);// }workbook = null;return ds;}}catch (Exception ex){return ds;}}

2）通过3个参数决定选取范围（需要另外判断范围从小到大）