private int GetSheetIndex(XlsUtil.IWorkbook workbook)
{
var whichSheet = _whichSheet ?? 1; // 1-based number or name string
if (whichSheet is int || whichSheet is long)
{
var whichSheetNum = Convert.ToInt32(whichSheet);
if (whichSheetNum < 1)
{
throw new Exception($"The worksheet number must be at least 1.");
}
return(whichSheetNum - 1);
}
else if (whichSheet is string name)
{
var names = workbook.ReadWorksheetNames();
var index = names.IndexOf(name);
if (index == -1)
{
throw new Exception($"The worksheet \"{name}\" was not found.");
}
else
{
return(index);
}
}
else
{
throw new Exception($"The \"which-sheet\" argument must be a number or a string.");
}
}
public static XlsWorksheetInfo Load(XlsUtil.IWorkbook workbook, int worksheetIndex)
{
workbook.SeekToWorksheet(worksheetIndex);
var fullCount = workbook.GetRowCount();
workbook.SeekToWorksheet(worksheetIndex);
var data = workbook.ReadSheet(maxRows: 1000);
DataTable dataTable = new();
int numColumns;
if (data.Count > 0)
{
numColumns = data.Max(x => x.Length);
for (var columnIndex = 0; columnIndex < numColumns; columnIndex++)
{
var columnLetter = XlsUtil.ConvertNumToColString(columnIndex);
dataTable.Columns.Add(columnLetter);
}
}
else
{
numColumns = 1;
dataTable.Columns.Add("A");
}
dataTable.BeginLoadData();
foreach (var row in data)
{
// Format numbers.
for (var i = 0; i < row.Length; i++)
{
if (row[i] is int intValue)
{
row[i] = $"{intValue:#,##0}";
}
else if (row[i] is long longValue)
{
row[i] = $"{longValue:#,##0}";
}
else if (row[i] is double doubleValue)
{
row[i] = $"{doubleValue:#,##0.####}";
}
}
if (row.Length == numColumns)
{
dataTable.LoadDataRow(row, true);
}
else
{
var extendedRow = new object[numColumns];
Array.Copy(row, extendedRow, row.Length);
dataTable.LoadDataRow(extendedRow, true);
}
}
dataTable.EndLoadData();
return(new() {
Index = worksheetIndex,
Name = workbook.GetSheetName(worksheetIndex),
DataTable = dataTable,
FullCount = fullCount,
});
}
