private List <List <SimpleExcelCell> > ProcessWorkSheet(Worksheet worksheet)
{
int usedColumnsCount = worksheet.UsedRange.Cells.Columns.Count; // Total columns count
int usedRowsCount = worksheet.UsedRange.Cells.Rows.Count; // Total rows count
int[] columnWidrhPixel = new int[usedColumnsCount];
#region Get every column width and save it in array.
for (int i = 1; i <= usedRowsCount; i++)
{
int thisRowColumnsCount = worksheet.UsedRange.Rows[i].Columns.Count;
if (thisRowColumnsCount < usedColumnsCount) // If thisRowColumnsCount less then usedColumnsCount, this row has merge column
{
continue;
}
for (int j = 1; j <= thisRowColumnsCount; j++)
{
columnWidrhPixel[j - 1] = Points2Pixels(worksheet.Cells[i, j].Width);
}
break; // If get all columns width, break out this loop
}
#endregion
#region Get excel value
List <List <SimpleExcelCell> > rowList = new List <List <SimpleExcelCell> >();
for (int i = 1; i <= usedRowsCount; i++)
{
List <SimpleExcelCell> columnList = new List <SimpleExcelCell>();
int columnsCount = worksheet.UsedRange.Rows[i].Columns.Count;
int widthTailIndex = usedColumnsCount;
int currentCellIndex = 0;
for (int j = 1; j <= columnsCount; j++)
{
System.Windows.Forms.Application.DoEvents();
// Check excel range(cell) merge and value status.
emCellStatus cellStatus = ConvertCellStatus(worksheet.Cells[i, j], j);
if (cellStatus == emCellStatus.UniqueCell || cellStatus == emCellStatus.MergeCellHeader)
{
string currentCellName = worksheet.Cells[i, j].Address.ToString();
string currentCellValue = worksheet.Cells[i, j].Value2 == null ? "" : worksheet.Cells[i, j].Value2.ToString();
columnList.Add(new SimpleExcelCell(currentCellName, currentCellValue, cellStatus));
columnList[currentCellIndex].CellWidthPixels = columnWidrhPixel[j - 1];
currentCellIndex++;
}
else if (cellStatus == emCellStatus.MergeCellBody)
{
columnList[currentCellIndex - 1].CellWidthPixels += columnWidrhPixel[j - 1];
continue;
}
else
{
throw new Exception("Error:emCellStatus = " + cellStatus.ToString());
}
}
rowList.Add(columnList);
}
#endregion
return(rowList);
}
public SimpleExcelCell(string strName, string strValue, emCellStatus cellStatus)
{
this.CellName = strName;
this.CellValue = strValue;
this.CellStatus = cellStatus;
}