public void IsCellDateTimeTest(int worksheetIndex, int rowIndex, int columnIndex, bool expectedResult)
{
var reader = new WorksheetReader(filepath);
var result = reader.CellAtCoordinatesContainsDateTime(
new ExcelCellCoordinates(worksheetIndex, rowIndex, columnIndex));
result
.Should()
.Be(expectedResult);
}
public IEnumerator <object[]> GetEnumerator()
{
var reader = new WorksheetReader(filepath);
yield return(new object[] { 0, 19, 1, new DateTime(year: 1999, month: 6, day: 12, hour: 13, minute: 46, second: 27) });
yield return(new object[] { 0, 17, 1, new DateTime(year: 2012, month: 3, day: 8, hour: 6, minute: 15, second: 0) });
yield return(new object[] { 0, 4, 1, new DateTime(year: 2017, month: 10, day: 25) });
}
public Row GetRow(uint rowIndex)
{
if (_worksheet == null)
{
throw new InvalidOperationException("Please choose worksheet by calling SetWorksheet() method");
}
var sheetData = _worksheet.Worksheet.GetFirstChild <SheetData>();
return(WorksheetReader.GetRow(sheetData, rowIndex));
}
public void CellReadingStringDataTest(int worksheetIndex, int rowIndex, int columnIndex, string expectedCellValue)
{
IWorksheetReader worksheetReader = new WorksheetReader(filepath);
var cellCoordinates = new ExcelCellCoordinates(worksheetIndex, rowIndex, columnIndex);
var cellValue = worksheetReader.GetCellText(cellCoordinates);
cellValue
.Should()
.Be(expectedCellValue);
}
public void CheckNumberOfWorksheets()
{
IWorksheetReader worksheetReader = new WorksheetReader(filepath);
var worksheets = worksheetReader.Worksheets.ToList();
var worksheetsCount = worksheets.Count();
var expectedNumberOfWorksheets = 3;
worksheets
.Should()
.HaveCount(expectedNumberOfWorksheets, $"the file contains {expectedNumberOfWorksheets} worksheets");
}
public void TryingToGetCellDataFromNonExistingWorksheetThrowsError(int worksheetIndex, Action <ExcelCellCoordinates> getCellDataAction)
{
IWorksheetReader worksheetReader = new WorksheetReader(filepath);
var coordinates = new ExcelCellCoordinates(worksheetIndex: worksheetIndex, rowIndex: 1, columnIndex: 1);
Action getCellTextAction = () => getCellDataAction(coordinates);
getCellTextAction
.Should()
.ThrowExactly <WorksheetReaderNonExistingWorksheetException>("worksheet index cannot be less than zero or higher than worksheets count")
.WithMessage($"There is no worksheet at index: {worksheetIndex}");
}
public void TryingToGetCellDataFromIncorrectColumnIndexThrowsError(int columnIndex, Action <ExcelCellCoordinates> getCellDataAction)
{
IWorksheetReader worksheetReader = new WorksheetReader(filepath);
var coordinates = new ExcelCellCoordinates(worksheetIndex: 1, rowIndex: 1, columnIndex: columnIndex);
Action getCellTextAction = () => getCellDataAction(coordinates);
getCellTextAction
.Should()
.ThrowExactly <WorksheetReaderColumnLesserThanOneException>("column index must be larger than or equal 1")
.WithMessage($"WorksheetReader tried to read column at index: {columnIndex}");
}
protected void btn_callExcelFile_Click(object sender, EventArgs e)
{
// 가상 경로 문자열을 정적 경로로 변경해 반환
Session["savePath"] = Server.MapPath("~/ForCheckExcelFile/");
if (fileUpload_excelFile.HasFile)
{
string fileExt = fileUpload_excelFile.FileName.Split('.')[1];
if (!fileExt.Equals("xlsx"))
{
lbl_uploadStatus.Text = "엑셀 파일만 업로드 가능합니다.";
lbl_uploadStatus.CssClass = "text-danger ml-2";
return;
}
btn_callExcelFile.Visible = false;
Session["filename"] = fileUpload_excelFile.FileName;
Session["filepath"] = Session["savePath"].ToString() + Session["filename"].ToString();
// 만약에 똑같은 파일명이 있으면
fileUpload_excelFile.SaveAs(Session["filepath"].ToString());
fileUpload_excelFile.Visible = false;
lbl_uploadStatus.Text = "지정한 파일이 업로드 되었습니다.";
lbl_uploadStatus.CssClass = "text-success ml-2";
btn_checkWord.Visible = true;
//callAlert(sender, "지정한 파일이 업로드 되었습니다.");
Session["workbookObj"] = new WorkbookSetting();
(Session["workbookObj"] as WorkbookSetting).selectExcelFile(Session["filepath"].ToString());
(Session["workbookObj"] as WorkbookSetting).selectWorksheet();
Session["worksheetReaderObj"] = new WorksheetReader(Session["workbookObj"] as WorkbookSetting);
Session["wordDataInfo"] = (Session["worksheetReaderObj"] as WorksheetReader).getWordDataInfo();
Session["words"] = (Session["wordDataInfo"] as DataInfo).getWords();
string wordString = String.Join(",", (Session["words"] as List <string>).ToArray());
ClientScript.RegisterStartupScript(this.GetType(), "setWord", String.Format("setWord('{0}')", wordString), true);
}
else
{
lbl_uploadStatus.Text = "파일을 먼저 업로드 해주세요.";
lbl_uploadStatus.CssClass = "text-danger ml-2";
//callAlert(sender, "파일을 먼저 업로드 해주세요.");
}
}
public void TryingReadingDateTimeFromCellWithAnotherDataTypeTest(int worksheetIndex, int rowIndex, int columnIndex)
{
IWorksheetReader worksheetReader = new WorksheetReader(filepath);
var cellCoordinates = new ExcelCellCoordinates(worksheetIndex, rowIndex, columnIndex);
Action getCellDateTimeAction = () => worksheetReader.GetCellDateTimeAsUTC(cellCoordinates);
getCellDateTimeAction
.Should()
.ThrowExactly <WorksheetReaderCellValueTypeException>("trying to read cell with non-dateTime format should raise an exception")
.WithMessage($"*worksheet index: {cellCoordinates.WorksheetIndex},* " +
$"*row index: {cellCoordinates.RowIndex},*" +
$"*column index {cellCoordinates.ColumnIndex}*" +
$"*Expected type of cell's value: {typeof(DateTime)}*");
}
public IEnumerator <object[]> GetEnumerator()
{
var reader = new WorksheetReader(filepath);
Action <ExcelCellCoordinates> getCellTextFunc = coordinates => reader.GetCellText(coordinates);
Action <ExcelCellCoordinates> getCellDateTimeFunc = coordinates => reader.GetCellDateTimeAsUTC(coordinates);
var getCellDataFuncs = new Action <ExcelCellCoordinates>[] { getCellTextFunc, getCellDateTimeFunc };
foreach (var func in getCellDataFuncs)
{
yield return(new object[] { 0, func });
yield return(new object[] { -1, func });
yield return(new object[] { -10, func });
}
}
private void DrawAllGraphs(int columns)
{
bool screenUpdating = Application.ScreenUpdating;
Application.ScreenUpdating = false;
Worksheet worksheet = Globals.Factory.GetVstoObject(this.Application.ActiveWorkbook.ActiveSheet);
WorksheetReader reader = new WorksheetReader(worksheet);
List <Table> tables = new List <Table>();
List <Data> dataList = new List <Data>();
GetGraphsAndTablesWorksheet();
Worksheet newWorksheet = (Worksheet)Globals.Factory.GetVstoObject(Application.ActiveWorkbook.Sheets["Wykresy i Tabele"]);
bool error = false;
String errorString = "";
List <String> parametersList = new List <String>();
for (int c = 0; c < columns && !error; ++c)
{
bool foundSomething = true;
int colTrans = 0;
int graphNum = 0;
while (foundSomething && !error)
{
int column = 1 + (c + columns * colTrans) * _COLUMNS_DISTANCE;
bool foundNext = true;
int rowPos = 1;
for (int rowsSearched = 0; rowsSearched < _MAX_SEARCH && ((Excel.Range)worksheet.Cells[rowPos, column]).Value2 == null
; ++rowsSearched)
{
++rowPos;
}
if (((Excel.Range)worksheet.Cells[rowPos, column]).Value2 == null)
{
foundNext = false;
}
foundSomething = foundNext;
while (foundNext && !error)
{
String blockType = null;
if (reader.IsString(rowPos, column))
{
blockType = reader.GetString(rowPos, column);
}
if (blockType == null || blockType[0] != '[' || blockType[blockType.Length - 1] != ']')
{
foundNext = false;
continue;
}
blockType = blockType.Substring(1, blockType.Length - 2);
switch (blockType)
{
case "parametry":
int parametersCount = (int)((Excel.Range)worksheet.Cells[rowPos, column + 1]).Value2;
while (parametersCount-- > 0)
{
++rowPos;
parametersList.Add((string)((Excel.Range)worksheet.Cells[rowPos, column + 1]).Value2);
}
break;
case "wykres":
++rowPos;
if (!reader.IsString(rowPos, column) || !reader.GetString(rowPos, column).Equals("nazwa:"))
{
error = true;
errorString = "Błąd przy 'nazwa:' w 'wykres' - wiersz " + rowPos;
break;
}
string name = reader.GetString(rowPos, column + 1);
++rowPos;
if (!reader.IsString(rowPos, column) || !reader.GetString(rowPos, column).Equals("dane:"))
{
error = true;
errorString = "Błąd przy 'dane:' w 'wykres' - wiersz " + rowPos;
break;
}
if (!reader.IsNumber(rowPos, column + 1))
{
error = true;
errorString = "Błąd przy 'dane:' w 'wykres' - wiersz " + rowPos;
break;
}
int dataCount = reader.GetInt(rowPos, column + 1);
++rowPos;
int seriesCount = 0;
while (reader.IsString(rowPos, column + seriesCount))
{
seriesCount++;
}
worksheet.Range[worksheet.Cells[rowPos, column], worksheet.Cells[rowPos + dataCount, column + seriesCount - 1]].Select();
rowPos += dataCount;
DrawGraphFromSelectedCells(graphNum++, c, name, "", newWorksheet);
break;
case "dane":
Data data = new Data();
++rowPos;
if (!reader.IsString(rowPos, column) || !reader.GetString(rowPos, column).Equals("parametry:") || !reader.IsNumber(rowPos, column + 1))
{
error = true;
errorString = "Błąd przy 'parametry:' w 'dane' - wiersz " + rowPos;
break;
}
parametersCount = (int)((Excel.Range)worksheet.Cells[rowPos, column + 1]).Value2;
while (parametersCount-- > 0)
{
++rowPos;
data.Parameters.Add(reader.GetString(rowPos, column), reader.GetInt(rowPos, column + 1));
}
++rowPos;
if (!reader.IsString(rowPos, column) || !reader.GetString(rowPos, column).Equals("wartości:") || !reader.IsNumber(rowPos, column + 1))
{
error = true;
errorString = "Błąd przy 'wartości:' w 'dane' - wiersz " + rowPos;
break;
}
int valuesCount = reader.GetInt(rowPos, column + 1);
while (valuesCount-- > 0)
{
++rowPos;
if (!reader.IsNumber(rowPos, column + 1))
{
error = true;
errorString = "Nie znaleziono liczby przy wartości - wiersz " + rowPos;
break;
}
data.Values.Add(reader.GetString(rowPos, column), reader.GetDouble(rowPos, column + 1));
}
dataList.Add(data);
break;
case "tabela":
Table table = new Table();
++rowPos;
if (!reader.IsString(rowPos, column) || !reader.GetString(rowPos, column).Equals("nazwa:"))
{
error = true;
errorString = "Błąd przy 'nazwa:' w 'tabela' - wiersz " + rowPos;
break;
}
name = reader.GetString(rowPos, column + 1);
table.Name = name;
++rowPos;
if (!reader.IsString(rowPos, column) || !reader.GetString(rowPos, column).Equals("parametry:") || !reader.IsNumber(rowPos, column + 1))
{
error = true;
errorString = "Błąd przy 'parametry:' w 'tabela' - wiersz " + rowPos;
break;
}
parametersCount = (int)((Excel.Range)worksheet.Cells[rowPos, column + 1]).Value2;
while (parametersCount-- > 0)
{
++rowPos;
table.Parameters.Add(reader.GetString(rowPos, column));
}
++rowPos;
if (!reader.IsString(rowPos, column) || !reader.GetString(rowPos, column).Equals("wartości:") || !reader.IsNumber(rowPos, column + 1))
{
error = true;
errorString = "Błąd przy 'wartość:' w 'tabela' - wiersz " + rowPos;
break;
}
valuesCount = reader.GetInt(rowPos, column + 1);
while (valuesCount-- > 0)
{
++rowPos;
table.Values.Add(reader.GetString(rowPos, column));
}
tables.Add(table);
break;
}
++rowPos;
for (int rowsSearched = 0; rowsSearched < _MAX_SEARCH && ((Excel.Range)worksheet.Cells[rowPos, column]).Value2 == null
; ++rowsSearched)
{
++rowPos;
}
if (((Excel.Range)worksheet.Cells[rowPos, column]).Value2 == null)
{
foundNext = false;
}
}
colTrans += 1;
}
}
if (error)
{
MessageBox.Show("błąd : " + errorString);
}
int tableColumn = _GRAPH_WIDTH * columns + 3;
int tableRow = 1;
foreach (Table table in tables)
{
//checking parameters range
List <List <int> > parametersRanges = new List <List <int> >();
foreach (string parameter in table.Parameters)
{
List <int> range = new List <int>();
foreach (Data data in dataList)
{
if (data.Parameters.ContainsKey(parameter))
{
int paramValue = data.Parameters[parameter];
if (!range.Contains(paramValue))
{
range.Add(paramValue);
}
}
}
range.Sort();
parametersRanges.Add(range);
}
if (table.Parameters.Count == 2)
{
//title
((Excel.Range)newWorksheet.Range[newWorksheet.Cells[tableRow, tableColumn], newWorksheet.Cells[tableRow, tableColumn + parametersRanges[0].Count + 1]]).Merge();
((Excel.Range)newWorksheet.Cells[tableRow, tableColumn]).Value2 = table.Name;
//first parameter title
((Excel.Range)newWorksheet.Range[newWorksheet.Cells[tableRow + 1, tableColumn + 2], newWorksheet.Cells[tableRow + 1, tableColumn + parametersRanges[0].Count + 1]]).Merge();
((Excel.Range)newWorksheet.Cells[tableRow + 1, tableColumn + 2]).Value2 = table.Parameters[0];
//first parameter range
for (int i = 0; i < parametersRanges[0].Count; ++i)
{
((Excel.Range)newWorksheet.Cells[tableRow + 2, tableColumn + 2 + i]).Value2 = parametersRanges[0][i];
}
//second parameter title
((Excel.Range)newWorksheet.Range[newWorksheet.Cells[tableRow + 3, tableColumn], newWorksheet.Cells[tableRow + 2 + parametersRanges[1].Count
, tableColumn]]).Merge();
((Excel.Range)newWorksheet.Cells[tableRow + 3, tableColumn]).Value2 = table.Parameters[1];
//second parameter range
for (int i = 0; i < parametersRanges[1].Count; ++i)
{
((Excel.Range)newWorksheet.Cells[tableRow + 3 + i, tableColumn + 1]).Value2 = parametersRanges[1][i];
}
for (int v1 = 0; v1 < parametersRanges[0].Count; ++v1)
{
for (int v2 = 0; v2 < parametersRanges[1].Count; ++v2)
{
bool found = false;
for (int d = 0; !found && d < dataList.Count; ++d)
{
if (dataList[d].Parameters.ContainsKey(table.Parameters[0]) && dataList[d].Parameters[table.Parameters[0]] == parametersRanges[0][v1] &&
dataList[d].Parameters.ContainsKey(table.Parameters[1]) && dataList[d].Parameters[table.Parameters[1]] == parametersRanges[1][v2] &&
dataList[d].Values.ContainsKey(table.Values[0]))
{
found = true;
((Excel.Range)newWorksheet.Cells[tableRow + 3 + v2, tableColumn + 2 + v1]).Value2 = dataList[d].Values[table.Values[0]];
}
}
}
}
tableRow += 3 + parametersRanges[1].Count + 1;
}
else if (table.Parameters.Count == 1)
{
//title
((Excel.Range)newWorksheet.Range[newWorksheet.Cells[tableRow, tableColumn], newWorksheet.Cells[tableRow, tableColumn + parametersRanges[0].Count]]).Merge();
((Excel.Range)newWorksheet.Cells[tableRow, tableColumn]).Value2 = table.Name;
//Parameter title
((Excel.Range)newWorksheet.Range[newWorksheet.Cells[tableRow + 1, tableColumn + 1], newWorksheet.Cells[tableRow + 1, tableColumn + parametersRanges[0].Count]]).Merge();
((Excel.Range)newWorksheet.Cells[tableRow + 1, tableColumn + 1]).Value2 = table.Parameters[0];
//Parameter range
for (int i = 0; i < parametersRanges[0].Count; ++i)
{
((Excel.Range)newWorksheet.Cells[tableRow + 2, tableColumn + i + 1]).Value2 = parametersRanges[0][i];
}
//Values
for (int v = 0; v < table.Values.Count; ++v)
{
//Value name
((Excel.Range)newWorksheet.Cells[tableRow + 3 + v, tableColumn]).Value2 = table.Values[v];
for (int v1 = 0; v1 < parametersRanges[0].Count; ++v1)
{
bool found = false;
for (int d = 0; !found && d < dataList.Count; ++d)
{
if (dataList[d].Parameters.ContainsKey(table.Parameters[0]) && dataList[d].Parameters[table.Parameters[0]] == parametersRanges[0][v1] &&
dataList[d].Values.ContainsKey(table.Values[v]))
{
found = true;
((Excel.Range)newWorksheet.Cells[tableRow + 3 + v, tableColumn + v1 + 1]).Value2 = dataList[d].Values[table.Values[v]];
}
}
}
}
tableRow += 3 + table.Values.Count;
}
}
Application.ScreenUpdating = screenUpdating;
}
protected override MemoryStream Write(WorksheetReader reader) => reader.Write();
public static IList <LocalizationItem> ImportFromXlsx(string fileName, out string errors)
{
int minRowCount;
if (Properties.Settings.Default.ImportContainsHeader == true)
{
minRowCount = 2;
}
else
{
minRowCount = 1;
}
errors = null;
FemtoXLSX.XlsxReader reader = new XlsxReader(fileName);
try
{
reader.Read();
}
catch (Exception e)
{
errors = e.Message;
return(null);
}
WorksheetReader worksheet = reader.GetWorksheet(0);
if (worksheet == null)
{
errors = "The worksheet could not be loaded";
return(null);
}
if (worksheet.HasColumn("G") == false)
{
errors = "Wrong number of columns. The worksheet must have at least 7 columns.";
return(null);
}
int rowCount = worksheet.GetRowCount();
if (rowCount < minRowCount)
{
if (minRowCount == 1)
{
errors = "No data found. The worksheet must have at least 1 data row (no header expected).";
}
else
{
errors = "No data found. The worksheet must have at least 2 rows (header + 1 data row).";
}
return(null);
}
Dictionary <string, Cell> data = worksheet.Data;
List <LocalizationItem> items = new List <LocalizationItem>();
LocalizationItem item;
List <Cell> row;
int[] mandatory = new int[] { 0, 1, 2, 3, 4, 6 };
for (int i = minRowCount - 1; i < rowCount; i++) // Start with row 2( index 1) or 1 (index 0)
{
row = worksheet.GetRow(i);
if (worksheet.RowHasColumns(ref row, mandatory) == false)
{
errors = "Row " + (i + 1).ToString() + " has too few values. At least 6 columns are expected.";
return(null);
}
item = new LocalizationItem();
foreach (Cell cell in row)
{
if (cell.ColumnNumber == 0)
{
item.StreamName = cell.GetStringValue();
}
else if (cell.ColumnNumber == 1)
{
item.ResourceKey = cell.GetStringValue();
}
else if (cell.ColumnNumber == 2)
{
item.ResourceCategory = cell.GetStringValue();
}
else if (cell.ColumnNumber == 3 && cell.Type == Cell.DataType.Boolean)
{
item.IsReadable = cell.GetBoolValue();
}
else if (cell.ColumnNumber == 4 && cell.Type == Cell.DataType.Boolean)
{
item.IsModifieable = cell.GetBoolValue();
}
else if (cell.ColumnNumber == 5)
{
item.Comment = cell.GetStringValue();
}
else if (cell.ColumnNumber == 6)
{
item.Content = cell.GetStringValue();
}
else if (cell.ColumnNumber < 7) // Error fall-back; higher numbers are ignored
{
errors = "Unexpected data occurred. Check the columns A to G in Row " + (i + 1).ToString();
return(null);
}
}
items.Add(item);
}
return(items);
}
