public static void exportExcelFile(TableExcelData data, string filePath) { var doc = new XmlDocument(); var root = doc.CreateElement("root"); doc.AppendChild(root); foreach (var row in data.Rows) { var item = doc.CreateElement("item"); for (int i = 0; i < data.Headers.Count; i++) { var hdr = data.Headers[i]; var val = row.StrList[i]; item.SetAttribute(hdr.FieldName, val); } root.AppendChild(item); } // 保存 using (FileStream fs = File.Create(filePath)) { var writer = new XmlTextWriter(fs, Encoding.UTF8); writer.Formatting = Formatting.Indented; doc.Save(writer); writer.Close(); } }
public static void ExportExcelFile(TableExcelData data, string filePath, string fileName) { var csString = new StringBuilder(); csString.AppendTemplate(fileName); File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(csString.ToString())); }
public static void exportExcelFile(TableExcelData data, string filePath) { List <Dictionary <string, object> > lst = ExportData(data); var indent = AppData.Config.OutputLuaWithIndent ? Formatting.Indented : Formatting.None; string output = SerializeWithCustomIndenting(lst); File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(output)); }
public static void ExportExcelFile(TableExcelData data, string filePath) { var csString = new StringBuilder(); csString.AppendPrologue(); csString.AppendTitle(filePath); csString.AppendProperty(data, string.Empty, 1); csString.AppendEnd(); File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(csString.ToString())); }
private static void _writeToDataSheet(ISheet sheet, TableExcelData data) { var lst = data.Headers.Select(a => a.FieldName).ToList(); _writeToSheet(sheet, lst); foreach (var row in data.Rows) { _writeToSheet(sheet, row.StrList); } }
public static void genExcel(TableExcelData data, string filePath) { Util.MakesureFolderExist(Path.GetDirectoryName(filePath)); var ext = Path.GetExtension(filePath).ToLower(); if (ext != ".xls" && ext != ".xlsx") { throw new Exception(string.Format("无法识别的文件扩展名 {0}", ext)); } var workbook = ext == ".xls" ? (IWorkbook) new HSSFWorkbook() : (IWorkbook) new XSSFWorkbook(); var sheet1 = workbook.CreateSheet(AppData.Config.SheetNameForField); var sheet2 = workbook.CreateSheet(AppData.Config.SheetNameForData); //创建新字体 var font = workbook.CreateFont(); font.IsBold = true; //创建新样式 var style = workbook.CreateCellStyle(); style.SetFont(font); style.FillPattern = FillPattern.SolidForeground; style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; //默认样式 for (short i = 0; i < workbook.NumCellStyles; i++) { workbook.GetCellStyleAt(i).VerticalAlignment = VerticalAlignment.Center; } _writeToDefSheet(sheet1, data.Headers); _writeToDataSheet(sheet2, data); var tmppath = Path.Combine(Path.GetDirectoryName(filePath), string.Format("{0}.tmp{1}", Path.GetFileNameWithoutExtension(filePath), ext)); using (var fs = File.Open(tmppath, FileMode.Create, FileAccess.Write, FileShare.ReadWrite)) { workbook.Write(fs); } var content = File.ReadAllBytes(tmppath); File.Delete(tmppath); File.WriteAllBytes(filePath, content); }
private static string BuildItemString(TableExcelData data) { var ids = new List <string>(); var keys = new List <string>(); var luaString = new StringBuilder(); foreach (var row in data.Rows) { for (int i = 0; i < data.Headers.Count; i++) { var hdr = data.Headers[i]; var val = row.StrList[i]; var name = hdr.FieldName.ToLower(); string s = string.Empty; if (name == "id") { int.TryParse(val, out int n); s = n.ToString(); ids.Add(s); } else if (name == "key") { s = string.Format("\"{0}\"", val); keys.Add(s); } } } appendFormatLineEx(luaString, 0, "local indexItems = "); appendFormatLineEx(luaString, 0, "{{"); for (int i = 0; i < ids.Count; i++) { appendFormatLineEx(luaString, 1, "[{0}] = items[{1}],", ids[i], i + 1); } appendFormatLineEx(luaString, 0, "}}"); luaString.AppendLine(); appendFormatLineEx(luaString, 0, "local idItems = "); appendFormatLineEx(luaString, 0, "{{"); for (int i = 0; i < keys.Count; i++) { appendFormatLineEx(luaString, 1, "[{0}] = items[{1}],", keys[i], i + 1); } appendFormatLineEx(luaString, 0, "}}"); luaString.AppendLine(); return(luaString.ToString()); }
private static void exportLua_Impl(TableExcelData data, string filePath) { var luaString = new StringBuilder(); luaString.Append(BuildCommentString(data.Headers)); appendFormatLineEx(luaString, 0, "local items ="); appendFormatLineEx(luaString, 0, "{{"); luaString.Append(BuildDataString(data, string.Empty, 2)); appendFormatLineEx(luaString, 0, "}}"); luaString.AppendLine(); luaString.Append(BuildItemString(data)); luaString.Append(BuildFuncString(Path.GetFileName(filePath))); File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(luaString.ToString())); }
private static void exportLua_Impl1(TableExcelData data, string filePath) { var sb = new StringBuilder(); appendFormatLineEx(sb, 0, "local data = "); appendFormatLineEx(sb, 0, "{{"); foreach (var row in data.Rows) { appendFormatLineEx(sb, 1, "{{"); for (int i = 0; i < data.Headers.Count; i++) { var hdr = data.Headers[i]; var val = row.StrList[i]; string s = string.Empty; switch (hdr.FieldType) { case "string": s = string.Format("\"{0}\"", val); break; case "int": { int n = 0; int.TryParse(val, out n); s = n.ToString(); } break; case "double": { double n = 0; double.TryParse(val, out n); s = n.ToString(); } break; } appendFormatLineEx(sb, 2, "[\"{0}\"] = {1},", hdr.FieldName, s); } appendFormatLineEx(sb, 1, "}},"); } appendFormatLineEx(sb, 0, "}}"); sb.AppendLine(); appendFormatLineEx(sb, 0, "return data"); File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(sb.ToString())); }
public static void exportExcelFile(TableExcelData data, string filePath) { List <Dictionary <string, object> > lst = data.Rows.Select(a => { var r = new Dictionary <string, object>(); for (int i = 0; i < data.Headers.Count; i++) { var hdr = data.Headers[i]; var val = a.StrList[i]; object obj = null; switch (hdr.FieldType) { case "string": obj = val; break; case "int": { int n = 0; int.TryParse(val, out n); obj = n; } break; case "double": { double n = 0; double.TryParse(val, out n); obj = n; } break; } r[hdr.FieldName] = obj; } return(r); }).ToList(); string output = JsonConvert.SerializeObject(lst, Formatting.Indented); File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(output)); }
private static TableExcelData _readDataFromWorkbook(IWorkbook wb, string defSheetName, int row, int column, string dataSheetName) { var rows = new List <TableExcelRow>(); var sheet1 = wb.GetSheet(defSheetName); if (sheet1 == null) { throw new Exception(string.Format("'{0}'工作簿不存在", defSheetName)); } var sheet2 = wb.GetSheet(dataSheetName); if (sheet2 == null) { throw new Exception(string.Format("'{0}'工作簿不存在", dataSheetName)); } //加载字段 var headers = _readHeadersFromDefSheet(sheet1, row, column, defSheetName); var h1 = headers.Find(a => a.FieldName.ToLower() == "id"); if (h1 == null) { throw new Exception(string.Format("'{0}'工作簿{1}行{2}列缺失id字段!", defSheetName, row, column + 1)); } var h2 = headers.Find(a => a.FieldName.ToLower() == "key"); if (h2 == null) { throw new Exception(string.Format("'{0}'工作簿{1}行{2}列缺失key字段!", defSheetName, row, column + 1)); } //加载数据 var headers2 = _readHeadersFromDataSheet(sheet2, dataSheetName); var headerIndexes = new int[headers.Count]; _checkFieldsSame(headers, headers2, headerIndexes, dataSheetName); foreach (var ds in _readDataFromDataSheet(sheet2, headers2.Count)) { var rowData = new List <string>(); for (int i = 0; i < headers.Count; i++) { var idx = headerIndexes[i]; rowData.Add(ds[idx]); } rows.Add(new TableExcelRow() { StrList = rowData }); } var data = new TableExcelData(headers, rows); for (int r = row; r <= sheet1.LastRowNum; r++) { var rd = sheet1.GetRow(r); if (rd == null) { continue; } var str1 = _convertCellToString(rd.GetCell(column + 0)); var str2 = _convertCellToString(rd.GetCell(column + 1)); if (string.IsNullOrEmpty(str2)) { continue; } else { var cell = rd.GetCell(column + 1); if (cell.Hyperlink != null && cell.Hyperlink.Type == HyperlinkType.Document) { string s = cell.Hyperlink.Address; char[] addr = s.ToCharArray(); int x = 0; int y = 0; if (s.Length == 6) { x = addr[5] - '0'; y = addr[4] - 'A'; } else { x = addr[6] - '0'; y = (addr[4] - 'A' + 1) * 26 + addr[5] - 'A'; } data.ChildData.Add(str1, _readDataFromWorkbook(wb, defSheetName, x, y, str1)); } } } return(data); }
private static string BuildDataString(TableExcelData data, string key, int iDeep) { bool bWithIndent = AppData.Config.OutputLuaWithIndent; var luaString = new StringBuilder(); int lbracket = 0, rbracket = 0;; foreach (var row in data.Rows) { if (key == string.Empty) { if (bWithIndent == true) { for (int t = 1; t <= iDeep - 1; t++) { luaString.Append(" "); } } else { luaString.Append(" "); } } if (key != string.Empty && key != row.StrList[1]) { continue; } if (bWithIndent == true) { luaString.Append("{\n"); } else { luaString.Append("{"); } lbracket++; for (int i = 0; i < data.Headers.Count; i++) { var hdr = data.Headers[i]; var name = hdr.FieldName.ToLower(); var val = row.StrList[i]; if (key != string.Empty && (name == "id" || name == "key")) { continue; } if (string.IsNullOrEmpty(val) && !(hdr.FieldType.Contains("group") || hdr.FieldType == "string" || hdr.FieldType == "table")) { continue; } string s = string.Empty; switch (hdr.FieldType) { case "string": s = string.Format("\"{0}\"", val); break; case "string(nil)": s = string.Format("\"{0}\"", val); break; case "int": { int.TryParse(val, out int n); s = n.ToString(); } break; case "double": { double.TryParse(val, out double n); s = n.ToString(); } break; case "bool": { bool.TryParse(val, out bool n); s = n == true ? "true" : "false"; } break; case var a when a.Contains("group"): { s = string.Format("{{{0}}}", val); } break; case "color": { s = string.Format("{0:X}", val); } break; case "table": { s = BuildDataString(data.ChildData[hdr.FieldName], row.StrList[1], iDeep + 1); } break; } if (!string.IsNullOrEmpty(s)) { if (bWithIndent == true) { for (int t = 1; t <= iDeep; t++) { luaString.Append(" "); } } if (name == "id") { luaString.AppendFormat("{0} = {1},", "index", s); } else if (name == "key") { luaString.AppendFormat("{0} = {1},", "id", s); } else { luaString.AppendFormat("{0} = {1},", name, s); } if (bWithIndent == true) { luaString.Append("\n"); } else { luaString.Append(" "); } } } if (key != string.Empty && bWithIndent == true) { for (int t = 1; t <= iDeep - 1; t++) { luaString.Append(" "); } } if (key == string.Empty) { if (bWithIndent == true) { luaString.AppendLine(" },\n"); } else { luaString.AppendLine("},"); } } else { luaString.Append("}"); } rbracket++; } if (lbracket != rbracket) { Log.Err("key: {2} Bracket mismatch {0} to {1}", lbracket, rbracket, key); } //else if (lbracket == 0) // luaString.Append("{}"); return(luaString.ToString()); }
public static void exportExcelFile(TableExcelData data, string filePath) { exportLua_Impl(data, filePath); }
private static void exportLua_Impl2(TableExcelData data, string filePath) { var sb = new StringBuilder(); var ids = new List <string>(); var keys = new List <string>(); //items appendFormatLineEx(sb, 0, "local items = "); appendFormatLineEx(sb, 0, "{{"); foreach (var row in data.Rows) { sb.Append(" { "); for (int i = 0; i < data.Headers.Count; i++) { var hdr = data.Headers[i]; var val = row.StrList[i]; string s = string.Empty; switch (hdr.FieldType) { case "string": s = string.Format("\"{0}\"", val); break; case "int": { int n = 0; int.TryParse(val, out n); s = n.ToString(); } break; case "double": { double n = 0; double.TryParse(val, out n); s = n.ToString(); } break; } sb.AppendFormat("{0} = {1}, ", hdr.FieldName, s); if (hdr.FieldName == "Id") { ids.Add(s); } else if (hdr.FieldName == "KeyName") { keys.Add(s); } } sb.AppendLine("},"); } appendFormatLineEx(sb, 0, "}}"); sb.AppendLine(); //idItems appendFormatLineEx(sb, 0, "local idItems = "); appendFormatLineEx(sb, 0, "{{"); for (int i = 0; i < ids.Count; i++) { appendFormatLineEx(sb, 1, "[{0}] = items[{1}],", ids[i], i + 1); } appendFormatLineEx(sb, 0, "}}"); sb.AppendLine(); //keyItems appendFormatLineEx(sb, 0, "local keyItems = "); appendFormatLineEx(sb, 0, "{{"); for (int i = 0; i < keys.Count; i++) { appendFormatLineEx(sb, 1, "[{0}] = items[{1}],", keys[i], i + 1); } appendFormatLineEx(sb, 0, "}}"); sb.AppendLine(); sb.Append( @"local data = { Items = items, IdItems = idItems, KeyItems = keyItems, } function data:getById(id) return self.IdItems[id] end function data:getByKey(key) return self.KeyItems[key] end return data "); File.WriteAllBytes(filePath, Encoding.UTF8.GetBytes(sb.ToString())); }
//-- 构造成员 --------------------------------------------------------------------------------------------------------------------------- private static void AppendProperty(this StringBuilder sb, TableExcelData data, string key, int deep) { for (int i = 0; i < data.Headers.Count; i++) { var header = data.Headers[i]; if (key != string.Empty && (header.FieldName.ToLower() == "id" || header.FieldName.ToLower() == "key")) { continue; } string type = header.FieldType; string name = header.FieldName; string desc = header.FieldDesc; if (desc != string.Empty) { if (name.ToLower() == "id") { desc = "唯一数字索引 (1~N)"; } else if (name.ToLower() == "key") { desc = "唯一字符串索引"; } sb.AppendComment(desc, deep); } if (type.Contains("group")) { type = GetGroupType(type, data.Rows[0].StrList[i]); sb.AppendListProperty(type, name, deep); } else if (type == "table") { sb.AppendNestedClassTitle(name, deep); sb.AppendProperty(data.ChildData[name], data.Rows[1].StrList[1], deep + 1); sb.AppendNestedClassEnd(deep); } else { if (name.ToLower() == "id") { name = "Index"; } if (name.ToLower() == "key") { name = "Id"; } if (type.ToLower() == "string(nil)") { type = "string"; } if (type.ToLower() == "double") { type = "float"; } if (type.ToLower() == "double(64)") { type = "double"; } if (type.ToLower() == "color") { type = "string"; } sb.AppendNormalProperty(type, name, deep); } if (i != data.Headers.Count - 1) { sb.AppendLine(); } } }
public static List <Dictionary <string, object> > ExportData(TableExcelData data) { List <Dictionary <string, object> > lst = new List <Dictionary <string, object> >(); foreach (var row in data.Rows) { var r = new Dictionary <string, object>(); for (int i = 0; i < data.Headers.Count; i++) { var hdr = data.Headers[i]; var val = row.StrList[i]; if (string.IsNullOrEmpty(val) && !(hdr.FieldType == "table")) { continue; } object obj = null; switch (hdr.FieldType) { case "string": case "string(nil)": case "color": obj = val; break; case "int": { int.TryParse(val, out int n); obj = n; } break; case "double": { double.TryParse(val, out double n); obj = n; } break; case "double(64)": { double.TryParse(val, out double n); obj = n; } break; case "bool": { bool.TryParse(val, out bool n); obj = n; } break; case var a when a.Contains("group"): { var str = val.Split(','); bool succeed = false; { var numlist = new List <int>(); foreach (var s in str) { succeed = int.TryParse(s, out int n); if (succeed == false) { break; } numlist.Add(n); } obj = numlist; } if (succeed == false) { var numlist = new List <double>(); foreach (var s in str) { succeed = double.TryParse(s, out double n); if (succeed == false) { break; } numlist.Add(n); } obj = numlist; } if (succeed == false) { var strlist = new List <string>(); foreach (var s in str) { strlist.Add(s.Replace("\"", string.Empty)); } obj = strlist; } } break; case "table": { obj = ExportDataSub(data.ChildData[hdr.FieldName], row.StrList[1]); break; } } if (hdr.FieldName.ToLower() == "id") { r["Index"] = obj; } else if (hdr.FieldName.ToLower() == "key") { r["Id"] = obj; } else { r[hdr.FieldName] = obj; } } lst.Add(r); } return(lst); }
public static TableExcelData ImportFile(string filePath) { byte[] content = GzipHelper.processGZipDecode(File.ReadAllBytes(filePath)); var ms = new MemoryStream(content); var br = new BinaryReader(ms); if (br.ReadInt32() != 1) { throw new Exception("无法识别的文件版本号"); } var r = new TableExcelData(); while (true) { var fieldName = br.ReadUtf8String(); if (string.IsNullOrEmpty(fieldName)) { break; } string fieldType; byte ftype = br.ReadByte(); switch (ftype) { case 1: fieldType = "int"; break; case 2: fieldType = "double"; break; case 3: fieldType = "string"; break; case 4: fieldType = "group"; break; case 5: fieldType = "bool"; break; case 6: fieldType = "color"; break; case 7: fieldType = "table"; break; case 8: fieldType = "string(nil)"; break; case 9: fieldType = "group(int)"; break; case 10: fieldType = "group(double)"; break; case 11: fieldType = "group(bool)"; break; case 12: fieldType = "group(string)"; break; case 13: fieldType = "double(64)"; break; default: throw new Exception(string.Format("无法识别的字段类型 fieldName:{0} fieldType:{1}", fieldName, ftype)); } r.Headers.Add(new TableExcelHeader() { FieldName = fieldName, FieldType = fieldType }); } while (br.BaseStream.Position < br.BaseStream.Length) { var lst = new List <string>(); for (int i = 0; i < r.Headers.Count; i++) { var hdr = r.Headers[i]; switch (hdr.FieldType) { case "string": lst.Add(br.ReadUtf8String()); break; case "int": lst.Add(br.ReadInt32().ToString()); break; case "double": lst.Add(br.ReadDouble().ToString()); break; case var a when a.Contains("group"): lst.Add(br.ReadUtf8String()); break; case "bool": lst.Add(br.ReadBoolean().ToString()); break; case "color": lst.Add(br.ReadInt32().ToString()); break; case "table": lst.Add(br.ReadUtf8String()); break; case "string(nil)": lst.Add(br.ReadUtf8String()); break; } } r.Rows.Add(new TableExcelRow() { StrList = lst }); } return(r); }
public static void genExcel(TableExcelData data, string filePath) { Util.MakesureFolderExist(Path.GetDirectoryName(filePath)); var ext = Path.GetExtension(filePath).ToLower(); if (ext != ".xls" && ext != ".xlsx") { throw new Exception(string.Format("无法识别的文件扩展名 {0}", ext)); } var workbook = ext == ".xls" ? (IWorkbook) new HSSFWorkbook() : (IWorkbook) new XSSFWorkbook(); var sheet = workbook.CreateSheet(AppData.Config.SheetNameForData); //创建新字体 var font = workbook.CreateFont(); font.IsBold = true; //创建新样式 var style = workbook.CreateCellStyle(); style.SetFont(font); style.FillPattern = FillPattern.SolidForeground; style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; //默认样式 for (short i = 0; i < workbook.NumCellStyles; i++) { workbook.GetCellStyleAt(i).VerticalAlignment = VerticalAlignment.Center; } try { IRow row0 = sheet.GetRow(0); IRow row1 = sheet.GetRow(1); IRow row2 = sheet.GetRow(2); if (row0 == null) { row0 = sheet.CreateRow(0); } if (row1 == null) { row1 = sheet.CreateRow(1); } if (row2 == null) { row2 = sheet.CreateRow(2); } row0.Height = 50; for (int i = 0; i < data.Headers.Count; i++) { var cell0 = row0.GetCell(i); var cell1 = row1.GetCell(i); var cell2 = row2.GetCell(i); if (cell0 == null) { cell0 = row0.CreateCell(i); } if (cell1 == null) { cell1 = row1.CreateCell(i); } if (cell2 == null) { cell2 = row2.CreateCell(i); } cell0.SetCellValue(data.Headers[i].FieldDesc); cell1.SetCellValue(data.Headers[i].FieldType); cell2.SetCellValue(data.Headers[i].FieldName); } } catch (Exception) { throw new Exception(string.Format("{0} 表创建失败!", filePath)); } var tmppath = Path.Combine(Path.GetDirectoryName(filePath), string.Format("{0}.tmp{1}", Path.GetFileNameWithoutExtension(filePath), ext)); using (var fs = File.Open(tmppath, FileMode.Create, FileAccess.Write, FileShare.ReadWrite)) { workbook.Write(fs); } var content = File.ReadAllBytes(tmppath); File.Delete(tmppath); File.WriteAllBytes(filePath, content); }
public static void exportExcelFile(TableExcelData data, string filePath) { const int version = 1; var ms = new MemoryStream(); var bw = new BinaryWriter(ms, Encoding.UTF8); bw.Write(version); foreach (var hdr in data.Headers) { bw.WriteUtf8String(hdr.FieldName); byte ftype = 0; switch (hdr.FieldType) { case "int": ftype = 1; break; case "double": ftype = 2; break; case "string": ftype = 3; break; default: throw new Exception(string.Format("无法识别的字段类型 {0} 名称 {1}", hdr.FieldType, hdr.FieldName)); } bw.Write(ftype); } bw.Write((byte)0); foreach (var row in data.Rows) { for (int i = 0; i < data.Headers.Count; i++) { var hdr = data.Headers[i]; var val = row.StrList[i]; switch (hdr.FieldType) { case "int": { int n = 0; int.TryParse(val, out n); bw.Write(n); } break; case "double": { double n = 0; double.TryParse(val, out n); bw.Write(n); } break; case "string": bw.WriteUtf8String(val); break; } } } if (File.Exists(filePath)) { File.Delete(filePath); } File.WriteAllBytes(filePath, GzipHelper.processGZipEncode(ms.GetBuffer(), (int)ms.Length)); bw.Close(); ms.Close(); }
public static TableExcelData importFile(string filePath) { var content = GzipHelper.processGZipDecode(File.ReadAllBytes(filePath)); var ms = new MemoryStream(content); var br = new BinaryReader(ms); if (br.ReadInt32() != 1) { throw new Exception("无法识别的文件版本号"); } var r = new TableExcelData(); while (true) { var fieldName = br.ReadUtf8String(); if (string.IsNullOrEmpty(fieldName)) { break; } var fieldType = string.Empty; var ftype = br.ReadByte(); switch (ftype) { case 1: fieldType = "int"; break; case 2: fieldType = "double"; break; case 3: fieldType = "string"; break; default: throw new Exception(string.Format("无法识别的字段类型 fieldName:{0} fieldType:{1}", fieldName, ftype)); } r.Headers.Add(new TableExcelHeader() { FieldName = fieldName, FieldType = fieldType }); } while (br.BaseStream.Position < br.BaseStream.Length) { var lst = new List <string>(); for (int i = 0; i < r.Headers.Count; i++) { var hdr = r.Headers[i]; switch (hdr.FieldType) { case "string": lst.Add(br.ReadUtf8String()); break; case "int": lst.Add(br.ReadInt32().ToString()); break; case "double": lst.Add(br.ReadDouble().ToString()); break; } } r.Rows.Add(new TableExcelRow() { StrList = lst }); } return(r); }