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);
}
