public static DataTable LoadExcelIntoDataTable(string filePath) { var doc = new SLDocument(filePath); var worksheetStats = doc.GetWorksheetStatistics(); var dataTable = PrepareDataTableForExcel(doc); for (var row = 2; row < worksheetStats.EndRowIndex; row++) { var dataRow = dataTable.NewRow(); for (var col = 0; col < worksheetStats.EndColumnIndex; col++) { dataRow[col] = doc.GetCellValueAsString(row, col); } dataTable.Rows.Add(dataRow); } return dataTable; }
public static Dictionary<int, string> GetIndexedColumnNames(SLDocument doc) { var indexedColumnNames = new Dictionary<int, string> { }; var worksheetStats = doc.GetWorksheetStatistics(); for (var i = 1; i <= worksheetStats.NumberOfColumns; i++) { indexedColumnNames.Add(i, doc.GetCellValueAsString(1, i)); } return indexedColumnNames; }
public static ExcelMetaData GetExcelMetaData(Stream ms) { var doc = new SLDocument(ms); var worksheetStats = doc.GetWorksheetStatistics(); var excelStats = new ExcelMetaData { RowCount = worksheetStats.NumberOfRows, IndexedColumnNames = GetIndexedColumnNames(doc) }; return excelStats; }
private void CacheStats(SLDocument doc) { SLWorksheetStatistics stats = doc.GetWorksheetStatistics(); numCols = stats.NumberOfColumns; numRows = stats.NumberOfRows; }
static void Main(string[] args) { if (args.Length < 2) { Console.WriteLine("Usage: ResourceTool.exe -i/e sample.xlsx"); return; } switch (args[0]) { case "-i": { var doc = new SLDocument(args[1]); doc.SelectWorksheet("Translations"); var stat = doc.GetWorksheetStatistics(); var languages = new List<string>(); for (var i = 3; i <= stat.EndColumnIndex; i++) { var lang = doc.GetCellValueAsString(2, i); var resxName = lang == "en-US" ? "Resources.resx" : string.Format("Resources.{0}.resx", lang); languages.Add(resxName); } for (var j = 0; j < languages.Count; j++) { var language = languages[j]; var resxFile = Path.Combine(RootPath, "Properties", language); var resx = new XmlDocument(); resx.Load(resxFile); for (var i = 3; i <= stat.EndRowIndex; i++) { var key = doc.GetCellValueAsString(i, 1); Console.WriteLine("{0} {1} {2}", i, key, language); var value = doc.GetCellValueAsString(i, j + 3); var node = resx.SelectSingleNode(string.Format(".//data[@name='{0}']", key)); if (node == null) { node = resx.CreateElement("data"); var name = resx.CreateAttribute("name"); name.Value = key; node.Attributes.Append(name); var xmlspace = resx.CreateAttribute("xml:space"); xmlspace.Value = "preserve"; node.Attributes.Append(xmlspace); resx.DocumentElement.AppendChild(node); var valueNode = resx.CreateElement("value"); valueNode.InnerText = value; node.AppendChild(valueNode); } else { var valueNode = node.SelectSingleNode("value"); if (valueNode == null) { valueNode = resx.CreateElement("value"); node.AppendChild(valueNode); } valueNode.InnerText = value; } } resx.Save(resxFile); } } break; case "-e": { if (File.Exists(args[1])) File.Delete(args[1]); var r = new Regex(@"Resources\.([a-z]{2}-[A-Z]{2})?\.?resx"); var doc = new SLDocument(); doc.AddWorksheet("Translations"); doc.DeleteWorksheet("Sheet1"); doc.SelectWorksheet("Translations"); doc.SetCellValue(1, 1, "Key"); doc.SetCellValue(1, 2, "Comment"); var headerStyle = doc.CreateStyle(); headerStyle.Font.Bold = true; headerStyle.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightBlue, System.Drawing.Color.LightBlue); doc.SetCellStyle(1, 1, headerStyle); doc.SetCellStyle(1, 2, headerStyle); var resxFiles = Directory.GetFiles(Path.Combine(RootPath, "Properties"), "*.resx"); var columnIndex = 3; var keys = new List<string>(); var files = resxFiles.OrderBy(n => { var l = r.Match(Path.GetFileName(n)).Groups[1].Value; return string.IsNullOrEmpty(l) ? null : n; }); foreach (var resx in files) { var lang = r.Match(Path.GetFileName(resx)).Groups[1].Value; var english = false; if (string.IsNullOrEmpty(lang)) { lang = "en-US"; english = true; } var ci = CultureInfo.GetCultureInfo(lang); doc.SetCellValue(1, columnIndex, ci.EnglishName); doc.SetCellValue(2, columnIndex, ci.Name); doc.SetCellStyle(1, columnIndex, headerStyle); doc.SetColumnWidth(columnIndex, 100); var rr = new ResXResourceReader(resx); if (english) { rr.UseResXDataNodes = true; var rowIndex = 3; foreach (DictionaryEntry entry in rr) { var key = (string) entry.Key; var node = (ResXDataNode) entry.Value; keys.Add(key); doc.SetCellValue(rowIndex, 1, key); doc.SetCellValue(rowIndex, 2, node.Comment); doc.SetCellValue(rowIndex, columnIndex, node.GetValue((ITypeResolutionService) null).ToString()); rowIndex++; } } else { rr.UseResXDataNodes = true; foreach (DictionaryEntry entry in rr) { var key = (string) entry.Key; var node = (ResXDataNode) entry.Value; var index = keys.IndexOf(key); int rowIndex; if (index == -1) { rowIndex = keys.Count; keys.Add(key); doc.SetCellValue(rowIndex, 1, key); } else { rowIndex = index + 3; } doc.SetCellValue(rowIndex, columnIndex, node.GetValue((ITypeResolutionService) null).ToString()); } } columnIndex++; } doc.AutoFitColumn(1, 2); doc.SaveAs(args[1]); } break; default: Console.WriteLine("Unknown switch {0}", args[0]); return; } }