public IObservable <Unit> WriteWorkSheetData(string[] header, string[][] data, string path) { if (header == null) { Observable.Throw <Unit>(new ArgumentNullException("header")); } //set our header row int rowLen = header.Length; for (int i = 0; i < rowLen; i++) { worksheet.SetCell <string>(i, 0, header[i]); } //set our data for (int i = 0; i < data.Length; i++) { { for (int j = 0; j < rowLen; j++) { string temp = data[i][j]; temp = temp.Replace(';', '-'); temp = temp.Replace(",", ""); worksheet.SetCell <string>(j, i + 1, temp); } } } //write our data to file worksheet.Save(SheetUtils.GetTimeStampedFileName(path, "csv")); return(Observable.ReturnUnit()); }
private static void WriteColumnsNames() { IRow row = SheetUtils.CreateRow(_sheet); row.Height = 900; int numColumns = _list.Columns.Count; for (int i = 0; i < numColumns; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(_list.Columns.ElementAt(i).Name); cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(_sheet.Workbook); } }
private static void CreateColumnMap(string sheetPath) { string assetPath = "Assets/Scripts/DataObjects"; if (!string.IsNullOrEmpty(sheetPath)) { if (!File.Exists(sheetPath)) { Debug.LogWarning(string.Format("Sheet Path {0} does not exist.", sheetPath)); return; } ExcelQuery q = new ExcelQuery(sheetPath); q.GetNewSheet(q.GetSheetNames()[0]); string err = string.Empty; string[] titles = q.GetTitle(0, ref err); if (err != string.Empty) { Debug.LogError(string.Format("Could not read sheet titles from first sheet {0} of {1}", q.GetSheetNames()[0], sheetPath)); return; } else { var bar = ScriptableObjectUtils.ObservableCreateAsset <ColumnMap>(Path.GetFileNameWithoutExtension(sheetPath) + "_ColumnMap", assetPath) .Do(map => { ScriptableObjectUtils.ObservableCreateAsset <RuleConstant>("WriteNothing", assetPath) .Subscribe(defaultRule => { int len = titles.Length; for (int i = 0; i < len; i++) { map.rules.Add(new RuleInfo() { description = string.Format("{0} - {1}", SheetUtils.GetColumnLettersFromIndex(i + 1), titles[i]), rule = defaultRule }); } }); map.header.AddRange(titles); }); bar.Subscribe(); } } }
IEnumerator <Marker> IEnumerable <Marker> .GetEnumerator() { foreach (var sheet in _sheets) { var rangeUsed = sheet.RangeUsed(); if (rangeUsed == null) { continue; } foreach (var row in rangeUsed.Rows()) { foreach (var cell in row.CellsUsed()) { if (CellUtils.IsMarkedCell(cell, _markerOptions)) { var markerId = CellUtils.ExtractMarkerValue(cell, _markerOptions); var isEndMarker = markerId.Substring(0, _markerOptions.Terminator.Length) == _markerOptions.Terminator; var marker = new Marker { Id = isEndMarker ? markerId.Substring(_markerOptions.Terminator.Length) : markerId, Position = new MarkerPosition { SheetIndex = SheetUtils.SheetIndex(sheet), RowIndex = row.FirstCell().Address.RowNumber, CellIndex = cell.Address.ColumnNumber }, MarkerType = isEndMarker ? MarkerType.End : MarkerType.Start }; yield return(marker); } } } } }
public void WriteData(AbstractList list, ISheet sheet) { ICollection <StringColumn> columns = list.Columns; int numDataChunks = columns.First().Data.Count; int numColumns = columns.Count; _lastDataColumn = numColumns - 1; for (int chunkIndex = 0; chunkIndex < numDataChunks; chunkIndex++) { int numEntries = columns.First().Data.ElementAt(chunkIndex).Entries.Count; for (int entryIndex = 0; entryIndex < numEntries; entryIndex++) { IRow row = SheetUtils.CreateRow(sheet); if (chunkIndex == 0 && entryIndex == 0) { _firstDataRow = row.RowNum; } string assemblyEntry = columns.First().Data.ElementAt(chunkIndex).Entries.ElementAt(entryIndex); bool isFirstRow = !string.IsNullOrEmpty(assemblyEntry); for (int columnIndex = 0; columnIndex < numColumns; columnIndex++) { ICell cell = row.CreateCell(columnIndex); cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(sheet.Workbook); string entry = columns.ElementAt(columnIndex).Data.ElementAt(chunkIndex).Entries .ElementAt(entryIndex); bool isNumber = double.TryParse(entry, NumberStyles.Any, CultureInfo.InvariantCulture, out double result); if (isNumber) { int decimalPts = columns.ElementAt(columnIndex).GetNumDecimalPlaces(); switch (decimalPts) { case -1: cell.SetCellValue(entry); break; case 0: cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle0DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; case 1: cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle1DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; default: cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle2DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; } } else { cell.SetCellValue(entry.Trim()); } if (isFirstRow) { if (isNumber) { int decimalPts = columns.ElementAt(columnIndex).GetNumDecimalPlaces(); switch (decimalPts) { case -1: cell.SetCellValue(entry); cell.CellStyle = CellStyleFactory.CreateSummaryStyle(sheet.Workbook); break; case 0: cell.CellStyle = CellStyleFactory.CreateSummaryStyle0DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; case 1: cell.CellStyle = CellStyleFactory.CreateSummaryStyle1DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; default: cell.CellStyle = CellStyleFactory.CreateSummaryStyle2DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; } } else { cell.CellStyle = CellStyleFactory.CreateSummaryStyle(sheet.Workbook); } } } } } IRow summaryRow = SheetUtils.CreateRow(sheet); int emptySummaryCount = 0; string weightColumnName = TranslateUtils.Translate("Weight"); for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++) { ICell cell = summaryRow.CreateCell(columnIndex); cell.CellStyle = CellStyleFactory.CreateFinalSummaryStyle(sheet.Workbook); if (columns.ElementAt(columnIndex).Name.Contains(weightColumnName)) { int decimalPts = columns.ElementAt(columnIndex).GetNumDecimalPlaces(); switch (decimalPts) { case -1: cell.SetCellValue(((StructuralList)list).WeightSummary); break; case 0: cell.CellStyle = CellStyleFactory.CreateFinalSummaryStyle0DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(((StructuralList)list).WeightSummary, NumberStyles.Any, CultureInfo.InvariantCulture)); break; case 1: cell.CellStyle = CellStyleFactory.CreateFinalSummaryStyle1DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(((StructuralList)list).WeightSummary, NumberStyles.Any, CultureInfo.InvariantCulture)); break; default: cell.CellStyle = CellStyleFactory.CreateFinalSummaryStyle2DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(((StructuralList)list).WeightSummary, NumberStyles.Any, CultureInfo.InvariantCulture)); break; } // cell.SetCellValue(SheetUtils.SeparateThousands(((StructuralList) list).WeightSummary, false)); } else { cell.SetCellValue("Suma całkowita"); emptySummaryCount++; } } CellRangeAddress region = new CellRangeAddress(sheet.LastRowNum, sheet.LastRowNum, 0, emptySummaryCount - 1); sheet.AddMergedRegion(region); _lastDataRow = sheet.LastRowNum; BorderDrawer.drawBorders(sheet, _firstDataRow, _lastDataRow, _lastDataColumn); }
public void WriteData(AbstractList list, ISheet sheet) { ICollection <StringColumn> columns = list.Columns; int numDataChunks = columns.First().Data.Count; int numColumns = columns.Count; _lastDataColumn = numColumns + 1; for (int i = 0; i < numDataChunks; i++) { int rowCount = columns.First().Data.ElementAt(i).Entries.Count; for (int r = 0; r < rowCount; r++) { IRow row = SheetUtils.CreateRow(sheet); if (i == 0 && r == 0) { _firstDataRow = row.RowNum; } for (int c = 0; c < numColumns; c++) { ICell cell = row.CreateCell(c); cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(sheet.Workbook); string entry = columns.ElementAt(c).Data.ElementAt(i).Entries.ElementAt(r); bool isNumber = double.TryParse(entry, NumberStyles.Any, CultureInfo.InvariantCulture, out double result); if (isNumber) { int decimalPts = columns.ElementAt(c).GetNumDecimalPlaces(); switch (decimalPts) { case -1: cell.SetCellValue(entry); break; case 0: cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle0DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; case 1: cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle1DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; default: cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle2DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; } } else { cell.SetCellValue(entry); } } for (int notesIndex = 0; notesIndex < 2; notesIndex++) { ICell notesCell = row.CreateCell(numColumns + notesIndex); // if () notesCell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(sheet.Workbook); } CellRangeAddress region = new CellRangeAddress(sheet.LastRowNum, sheet.LastRowNum, numColumns - 1, numColumns + 1); sheet.AddMergedRegion(region); } if (i == numDataChunks - 1) { continue; } IRow blankRow = SheetUtils.CreateRow(sheet); for (int c = 0; c < numColumns + 2; c++) { ICell cell = blankRow.CreateCell(c); cell.CellStyle = CellStyleFactory.CreateFinalSummaryStyle(sheet.Workbook); } CellRangeAddress blankRegion = new CellRangeAddress(sheet.LastRowNum, sheet.LastRowNum, numColumns - 1, numColumns + 1); sheet.AddMergedRegion(blankRegion); } _lastDataRow = sheet.LastRowNum; BorderDrawer.drawBorders(sheet, _firstDataRow, _lastDataRow, _lastDataColumn); }
List <Dictionary <string, int> > GetALUSets(ExcelQuery query, bool compareRows, List <string> searchTerms, List <string> excludes) { List <Dictionary <string, int> > set = new List <Dictionary <string, int> >(); List <List <string> > list = new List <List <string> >(); string err = string.Empty; //Get 1st sheet string[] sheets = query.GetSheetNames(); if (sheets == null || sheets.Length > 1) { Debug.LogError("Sheet missing or more than one sheet! Quitting"); return(null); } //use first sheet in our query, whatever it is named query.GetNewSheet(sheets[0]); //Get titles string[] titles = query.GetTitle(0, ref err); if (titles == null) { Debug.Log("No titles found! Quitting"); return(null); } int len = titles.Length; int uid_col = SheetUtils.FindMatchIndexesInStringArr(titles, UID_ColumnNames.ToArray())[0]; if (uid_col < 0) { Debug.LogError(string.Format("couldn't find UID column")); return(null); } else { Debug.Log(string.Format("Found uid_column at {0} which is header {1}", uid_col, query.GetHeaderColumnName(uid_col))); } HashSet <int> skips = new HashSet <int>(SheetUtils.FindMatchIndexesInStringArr(titles, UPC_ColumnNames.ToArray())); //search titles for "UPC" or "Barcode" for (int col = 0; col < len; col++) { foreach (string heading in searchTerms) { if (titles[col].Contains(heading, StringComparison.OrdinalIgnoreCase)) { bool exclude = false; foreach (string exclusion in excludes) { if (titles[col].Contains(exclusion, StringComparison.OrdinalIgnoreCase)) { exclude = true; } } if (exclude) { continue; } Dictionary <string, int> column = new Dictionary <string, int>(); set.Add(column); List <string> sublist = new List <string>(); list.Add(sublist); //add all values for that column to dictionary with row as value string[] vals = query.GetColumn <string>(col, "(_._)"); for (int row = 0; row < vals.Length; row++) { string val = vals[row]; if (val == "(_._)") { continue; //skip our null sentinel } sublist.Add(val); if (column.ContainsKey(val)) { if (compareRows) { LogResult(string.Format("found {0} misses between row {1} and row {2}", Compare_Rows_Skip_Mismatched_UPC_If_ItemIDs_Equal(query, row + 1, column[val] + 1, titles.Length, uid_col, skips), column[val] + 1, row + 1)); } else { LogResult(string.Format("Current row:{0} Subset for column:{1} already contains val:{2} at row:{3}", row + 2, query.GetHeaderColumnName(col), val, column[val] + 2)); } } else { column.Add(val, row); } } } } } aluColumns.Add(list); return(set); }
void ColumnCheck(ExcelQuery query, string heading) { string err = string.Empty; //use first sheet in our query, whatever it is named query.GetNewSheet(query.GetSheetNames()[0]); //Get titles string[] titles = query.GetTitle(0, ref err); if (titles == null) { Debug.Log("No titles found! Quitting"); return; } int len = titles.Length; int uid_col = SheetUtils.FindMatchIndexesInStringArr(titles, UID_ColumnNames.ToArray())[0]; if (uid_col < 0) { Debug.LogError(string.Format("couldn't find UID column")); return; } else { Debug.Log(string.Format("Found uid_column at {0} which is header {1}", uid_col, query.GetHeaderColumnName(uid_col))); } for (int col = 0; col < len; col++) { if (titles[col].Equals(heading, StringComparison.OrdinalIgnoreCase)) { Dictionary <long, int> column = new Dictionary <long, int>(); //add all values for that column to dictionary with row as value long[] vals = query.GetColumn <long>(col, -1); for (int row = 0; row < vals.Length; row++) { long val = vals[row]; if (val == -1) { continue; //skip our null sentinel } if (column.ContainsKey(val)) { string[] duplicate = query.GetRow(row + 1, "(_._)", len); string[] original = query.GetRow(column[val] + 1, "(_._)", len); int result = CompareRows(duplicate, original); if (result < 3 && original[uid_col] != duplicate[uid_col]) { LogResult(string.Format("Problem! found almost duplicate upc and row (misses:{0}) with non-identical itemids: row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col])); continue; } if (result > 2 && original[uid_col] == duplicate[uid_col]) { LogResult(string.Format("Problem! found duplicate upc with non-duplicate rows (misses:{0}) but with identical itemids: row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col])); continue; } if (result < 3 && result > 0) { LogResult(string.Format("found almost duplicate upc/itemid (misses:{0}) we expect item:{1} at row:{2} will not be added because item:{3} exists at row:{4}", result, duplicate[uid_col], row + 1, original[uid_col], column[val] + 1)); continue; } if (result < 1) { LogResult(string.Format("found duplicate upc/itemid (misses:{0}) we expect item:{1} at row:{2} will not be added because item:{3} exists at row:{4}", result, duplicate[uid_col], row + 1, original[uid_col], column[val] + 1)); continue; } if (result > 2) { LogResult(string.Format("found non-duplicate items with the same UPC: {0} misses between row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col])); continue; } LogResult(string.Format("somehow managed to miss a case: found {0} misses between row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col])); } else { column.Add(val, row); } } } } return; }
public void WriteData(AbstractList list, ISheet sheet) { ICollection <StringColumn> columns = list.Columns; int numDataChunks = columns.First().Data.Count; int numColumns = columns.Count; _lastDataColumn = numColumns - 1; for (int i = 0; i < numDataChunks - 1; i++) { int rowCount = columns.First().Data.ElementAt(i).Entries.Count; for (int r = 0; r < rowCount; r++) { IRow row = SheetUtils.CreateRow(sheet); if (i == 0 && r == 0) { _firstDataRow = row.RowNum; } for (int c = 0; c < numColumns; c++) { ICell cell = row.CreateCell(c); cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(sheet.Workbook); string entry = columns.ElementAt(c).Data.ElementAt(i).Entries.ElementAt(r); bool isNumber = double.TryParse(entry, NumberStyles.Any, CultureInfo.InvariantCulture, out double result); if (isNumber) { int decimalPts = columns.ElementAt(c).GetNumDecimalPlaces(); switch (decimalPts) { case -1: cell.SetCellValue(entry); break; case 0: cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle0DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; case 1: cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle1DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; default: cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle2DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture)); break; } } else { cell.SetCellValue(entry); } } } IRow summaryRow = SheetUtils.CreateRow(sheet); int emptySummaryCount = 0; bool isFinal = i == numDataChunks - 2; ICellStyle summaryStyle = isFinal ? CellStyleFactory.CreateFinalSummaryStyle(sheet.Workbook) : CellStyleFactory.CreateSummaryStyle(sheet.Workbook); string sumLabel = isFinal ? "Suma całkowita" : "Suma"; for (int c = 0; c < numColumns; c++) { string summary = columns.ElementAt(c).Data.ElementAt(i).Summary; ICell cell = summaryRow.CreateCell(c, CellType.Numeric); cell.CellStyle = summaryStyle; if (string.IsNullOrEmpty(summary) || summary.Contains("Tota") || summary.Contains("for")) { emptySummaryCount++; cell.SetCellValue(sumLabel); } else { string columnName = columns.ElementAt(c).Name; bool isNumber = double.TryParse(summary, NumberStyles.Any, CultureInfo.InvariantCulture, out double result); if (isNumber) { int decimalPts = columns.ElementAt(c).GetNumDecimalPlaces(); switch (decimalPts) { case -1: cell.SetCellValue(summary); break; case 0: cell.CellStyle = isFinal ? CellStyleFactory.CreateFinalSummaryStyle0DecimalPts(sheet.Workbook) : CellStyleFactory.CreateSummaryStyle0DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(summary, NumberStyles.Any, CultureInfo.InvariantCulture)); break; case 1: cell.CellStyle = isFinal ? CellStyleFactory.CreateFinalSummaryStyle1DecimalPts(sheet.Workbook) : CellStyleFactory.CreateSummaryStyle1DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(summary, NumberStyles.Any, CultureInfo.InvariantCulture)); break; default: cell.CellStyle = isFinal ? CellStyleFactory.CreateFinalSummaryStyle2DecimalPts(sheet.Workbook) : CellStyleFactory.CreateSummaryStyle2DecimalPts(sheet.Workbook); cell.SetCellValue(double.Parse(summary, NumberStyles.Any, CultureInfo.InvariantCulture)); break; } // cell.SetCellValue(SheetUtils.SeparateThousands(summary, // !intColumns.Any(column => columnName.Contains(TranslateUtils.Translate(column))))); } else { emptySummaryCount++; } } if (!string.IsNullOrEmpty(summary) && summary.Contains("for") && summary.Length == 3) { emptySummaryCount++; } } CellRangeAddress region = new CellRangeAddress(sheet.LastRowNum, sheet.LastRowNum, 0, emptySummaryCount - 1); sheet.AddMergedRegion(region); } _lastDataRow = sheet.LastRowNum; BorderDrawer.drawBorders(sheet, _firstDataRow, _lastDataRow, _lastDataColumn); }