目录
需求描述
用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); //遍历DataSet
if (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); //遍历DataSet
if (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); //遍历DataSet
if (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;
}
#endregion3.转换方式二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,3
int 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;//Sheet1
si.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 Input
string 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].Col
firstrow = 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个参数决定选取范围（需要另外判断范围从小到大）
DEMO源码（包含我测试的两个excel文件）
附：Form1是我拿来测试截取单元格的小玩意儿~包含最基础的循环和正则，可以当作小彩蛋吧~
参考资料：
C#将List集合转换为json字符串_c#list转换成json-CSDN博客
C#中把Datatable转换为Json的5个代码实例_c# html 源码 获取table转json-CSDN博客
C# 使用 NPOI 库读写 Excel 文件_c# npoi读取excel数据-CSDN博客
C# dataGridView绑定List数据-CSDN博客
winform DataGridView绑定List<T>类型数据_winfrom t-CSDN博客
C#中WinForm制作点击"选择文件"按钮选择Excel文件, 并读取Excel内容到DataTable_winform click事件从文件读取列表-CSDN博客
C# 通过行和列直接读取Excel中数据的方法_c#获取excel每一列已使用单元格数量-CSDN博客
c#中List<Object>取出对应的值----dynamic_c# 获取 object 转 dynamic-CSDN博客
在C#中使用正则表达式提取括号中的内容_c# 正则表达式 匹配括号中内容-CSDN博客
C#从字符串中批量截取中间字符串_c# 获取字符串中所有td中间的内容-CSDN博客
C#获取动态key的json对象的值_c# 获取json对象中的值和key-CSDN博客
扩展阅读：
C#中如何将List<自定义>转为Json格式 及相关函数-DataContractJsonSerializer_c# 数组转json-CSDN博客
使用NPOI读取Excel到DataTable_npoi读取excel到datable并设置列数据类型-CSDN博客
java-读取Excel文件，自定义读取固定行、列（不区分xls、xlsx）_java excel自定义行列-CSDN博客 NPOI使用手册-CSDN博客
扫一扫
2193
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
