/// <summary> /// 根据excel 解析数据类型 /// </summary> /// <param name="title">列头</param> /// <param name="notes">注释</param> /// <param name="data">数据</param> /// <param name="model">返回值</param> /// <returns></returns> bool GetExcelPropertyType(ICell title, ICell notes, ICell data, ref ExcelRow model) { if (title == null || string.IsNullOrWhiteSpace(title.ToString())) { return(false); } string property = title.StringCellValue; string[] types = property.Split('='); property = property.ToUpper(); if (property.IndexOf("=HL") >= 0) { return(false); } ExcelCell at = new ExcelCell(); at.CellName = ReplaceSideLine(types[0]).TrimStart(Convert.ToChar(" ")).TrimEnd(Convert.ToChar(" ")); at.CellNotes = notes == null ? "" : notes.ToString().Replace("\n", "").Replace("\r", ""); //查找关联的文件名 if (property.IndexOf("=FF_") >= 0) { foreach (string item in types) { if (item.ToUpper().IndexOf("FF_") == 0) { //表示存在关联数据 at.CellFileName = item.Substring(3); at.CellFileName = this.ReplaceSideLine(at.CellFileName); break; } } } if (property.IndexOf("=B") >= 0) { //bool at.CellValueType = "Boolean"; } else if (property.IndexOf("=D") >= 0) { //double at.CellValueType = "double"; } else if (property.IndexOf("=S") >= 0) { at.CellValueType = "String"; if (property.IndexOf("=S_") >= 0) { foreach (string item in types) { if (item.ToUpper().IndexOf("S_") == 0) { //表示存在字符长度指定 string strLen = item.Substring(2); int len; if (int.TryParse(strLen, out len)) { at.CellLength = len; } break; } } } if (at.CellLength == 0) { at.CellLength = 255; } } else if (property.IndexOf("=FL") >= 0) { at.CellValueType = "float"; } else if (property.IndexOf("=L") >= 0) { at.CellValueType = "long"; } else { //int at.CellValueType += "int"; } switch (at.CellValueType) { case "Boolean": { double _value = data == null ? 0 : data.NumericCellValue; at.CellValue = ((int)_value).ToString(); } break; case "String": { string value1 = ""; if (data != null) { //string value = data.ToString(); //byte[] strBuffers = System.Text.UTF8Encoding.Default.GetBytes(value); //value1 = System.Text.UTF8Encoding.Default.GetString(strBuffers); value1 = data.ToString(); } at.CellValue = value1; //at.CellValue = data == null ? "" : System.Text.UTF8Encoding.Default.GetString(System.Text.UTF8Encoding.Default.GetBytes(data.ToString())); } break; case "double": { at.CellValue = data == null ? "0.00" : data.NumericCellValue.ToString(); } break; case "float": { at.CellValue = data == null ? "0.00" : data.NumericCellValue.ToString(); } break; case "long": { double _value = data == null ? 0 : data.NumericCellValue; at.CellValue = ((long)_value).ToString(); } break; case "int": { double _value = data == null ? 0 : data.NumericCellValue; at.CellValue = ((int)_value).ToString(); } break; } //去除前置后置空格,去除换行符 at.CellValue = at.CellValue.Replace("\n", "").Replace("\r", "").Trim(); //标识主键 if (property.IndexOf("=P") >= 0) { at.IsPKey = true; model.AtKey = at.CellValue; } if (property.IndexOf("=AC") >= 0) { at.Cellgs = "AC"; } else if (property.IndexOf("=AS") >= 0) { at.Cellgs = "AS"; } else { at.Cellgs = "ALL"; } model.Cells.Add(at); return(true); }
public void Add(string key, ExcelRow row) { //row.Cells.Sort(new ExcelCellComparer()); Rows.Add(key, row); }
public bool ReadExcelDataToCache(List <string> files, out FileExcelDatas exceldatas, Action <string> show, bool isHebing = false) { exceldatas = new FileExcelDatas(); if (files == null || files.Count == 0) { return(false); } Dictionary <string, HashSet <string> > filename_and_key = new Dictionary <string, HashSet <string> >(); show("开始处理 Excel 文件"); foreach (var excelPath in files) { ExcelDatas exceldata = new ExcelDatas(); try { using (FileStream fs = File.OpenRead(excelPath)) //打开myxls.xls文件 { HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中 if (wk.NumberOfSheets > 0) { var table = wk.GetSheetAt(0); string _sheetName = this.ReplaceSideLine(table.SheetName).ToLower(); show("开始分析文件:" + Path.GetFileName(excelPath) + " 文件数据"); if (table != null) { if (table.LastRowNum < 2) { show("文件格式是第一行是字段名,第二行是注释,第三行是数据"); continue; } IRow rowTitle = table.GetRow(0); //读取头 IRow rowNontes = table.GetRow(1); //读取配置说明 for (int i = 2; i <= table.LastRowNum; i++) { IRow rowData = table.GetRow(i); //读取当前行数据 if (rowData != null) { // 处理如果该行为空值那么忽略 // todo 考虑怎么添加 for (int k = 0; k < rowTitle.LastCellNum; k++) { ICell cell2 = rowData.GetCell(k); //当前表格 if (cell2 != null && !string.IsNullOrWhiteSpace(cell2.ToString())) { goto lab_xx; } } goto lab_Next; lab_xx: ExcelRow model = new ExcelRow(); for (int k = 0; k < rowTitle.LastCellNum; k++) //LastCellNum 是当前行的总列数 { try { GetExcelPropertyType(rowTitle.GetCell(k), rowNontes.GetCell(k), rowData.GetCell(k), ref model); } catch { show("文件:" + Path.GetFileName(excelPath) + " 取值错误 第 " + (i + 1) + " 行 " + " 第 " + (k + 1) + " 列 非数字类型"); return(false); } } if (string.IsNullOrWhiteSpace(model.AtKey)) { show("文件:" + Path.GetFileName(excelPath) + " 缺少主键"); return(false); } if (exceldata.Rows.ContainsKey(model.AtKey)) { show("文件:" + Path.GetFileName(excelPath) + " 存在重复主键 " + model.AtKey + " 第 " + (i + 1) + " 行 "); return(false); } if (model.Cells.Where(l => l.IsPKey).Count() > 1) { show("文件:" + Path.GetFileName(excelPath) + " 不支持双主键设置"); return(false); } exceldata.Add(model.AtKey, model); } } lab_Next: exceldata.SheetName = _sheetName; string datakey = Path.GetFileNameWithoutExtension(excelPath); if (!exceldatas.Datas.ContainsKey(datakey)) { //存储数据 exceldatas.Datas.Add(datakey, exceldata); } else { foreach (var row in exceldata.Rows) { if (exceldatas.Datas[datakey].Rows.ContainsKey(row.Key)) { show("文件:" + Path.GetFileName(excelPath) + " 存在重复主键 " + row.Key); return(false); } exceldatas.Datas[datakey].Rows.Add(row.Key, row.Value); } } { //存储模型 if (!exceldatas.SheetName_And_FileName.ContainsKey(_sheetName)) { exceldatas.SheetName_And_FileName.Add(_sheetName, new HashSet <string>()); } exceldatas.SheetName_And_FileName[_sheetName].Add(datakey); //存储模型 if (!exceldatas.FileName_And_SheetName.ContainsKey(datakey)) { exceldatas.FileName_And_SheetName.Add(datakey, new HashSet <string>()); } exceldatas.FileName_And_SheetName[datakey].Add(_sheetName); } if (!_sheetName.Equals(datakey)) { { //存储数据 if (!exceldatas.Datas.ContainsKey(_sheetName)) { exceldatas.Datas[_sheetName] = new ExcelDatas() { SheetName = _sheetName }; } foreach (var row in exceldata.Rows) { if (exceldatas.Datas[_sheetName].Rows.ContainsKey(row.Key)) { show("文件:" + Path.GetFileName(excelPath) + " 存在重复主键 " + row.Key); return(false); } exceldatas.Datas[_sheetName].Rows.Add(row.Key, row.Value); } } } //{ // //存储模型 // if (!filename_and_key.ContainsKey(_sheetName)) // { // filename_and_key.Add(_sheetName, new HashSet<string>()); // } // filename_and_key[_sheetName].Add(datakey); // foreach (var itemkey in filename_and_key[_sheetName]) // { // if (itemkey == datakey) // { // continue; // } // if (!exceldatas.Datas.ContainsKey(itemkey)) // { // exceldatas.Datas[itemkey] = new ExcelDatas() { SheetName = _sheetName }; // } // foreach (var row in exceldatas.Datas[_sheetName].Rows) // { // if (exceldatas.Datas[itemkey].Rows.ContainsKey(row.Key)) // { // show("文件:" + Path.GetFileName(excelPath) + " 存在重复主键 " + row.Key); // return false; // } // exceldatas.Datas[itemkey].Rows.Add(row.Key, row.Value); // } // } //} } } } } catch (Exception) { show("请确保是2003或者wps创建的excel文件并且处于关闭状态:" + Path.GetFileName(excelPath)); return(false); } } return(true); }