public IEnumerable <Tuple <string, MSheet> > FetchSheetDict()
{
Sheets sheets = this.workbook.Sheets;
foreach (Worksheet sheet in sheets)
{
string sheetName = sheet.Name;
List <Tuple <int, int, int, int> > ranges = TableHelper.SplitTable(sheet);
foreach (Tuple <int, int, int, int> range in ranges)
{
MSheet mSheet = new MSheet();
int upRow = range.Item1;
int leftCol = range.Item2;
int downRow = range.Item3;
int rightCol = range.Item4;
int rowNum = downRow - upRow + 1;
int colNum = rightCol - leftCol + 1;
mSheet.StartRow = upRow;
mSheet.StartCol = leftCol;
mSheet.RowNum = rowNum;
mSheet.ColNum = colNum;
{
Range beginCell = sheet.Cells[upRow, leftCol];
Range endCell = sheet.Cells[downRow, rightCol];
string beginAddress = beginCell.get_Address().Replace("$", "");
string endAddress = endCell.get_Address().Replace("$", "");
mSheet.Cells = sheet.get_Range(beginAddress, endAddress);
}
bool[,] vis = new bool[rowNum, colNum];
DataType[,] typeTable = new DataType[rowNum, colNum];
for (int i = 0; i < rowNum; ++i)
{
for (int j = 0; j < colNum; ++j)
{
vis[i, j] = false;
typeTable[i, j] = DataType.NONE;
}
}
for (int row = upRow; row <= downRow; ++row)
{
for (int col = leftCol; col <= rightCol; ++col)
{
if (vis[row - upRow, col - leftCol])
{
continue;
}
Range cell = sheet.Cells[row, col];
if (cell.MergeCells)
{
int rowCount = cell.MergeArea.Rows.Count;
int colCount = cell.MergeArea.Columns.Count;
mSheet.AddMergeCell(row, row + rowCount - 1, col, col + colCount - 1);
for (int i = row; i < row + rowCount; ++i)
{
for (int j = col; j < col + colCount; ++j)
{
vis[i - upRow, j - leftCol] = true;
}
}
}
string cellValue = Convert.ToString(cell.Value2);
string cellType = (cell.NumberFormat as string);
if (cellValue == null || cellValue.Length == 0)
{
continue;
}
string cType = this.getValueType(cellValue);
int indents = cell.IndentLevel;
/* XlHAlign
* -4131 = xlHAlignLeft -> ALIGN_LEFT = 0x1
* -4152 = xlHAlignRight -> ALIGN_RIGHT = 0x3
* -4108 = xlHAlignCenter -> ALIGN_CENTER = 0x2
* -4130 = xlHAlignJustify -> ALIGN_JUSTIFY = 0x5
* -4117 = xlHAlignDistributed ->
* 1 = xlHAlignGeneral -> ALIGN_GENERAL = 0x0
* 5 = xlHAlignFill -> ALIGN_FILL = 0x4
* 7 = xlHAlignCenterAcrossSelection ->
*/
// int alignStyle = cell.HorizontalAlignment;
// int alignStyle = this.getFeatureAlignStyle(cell.HorizontalAlignment);
int alignStyle = cell.HorizontalAlignment;
/* XlLineStyle
* -4142 = xlLineStyleNone -> BORDER_NONE = 0x0
* -4119 = xlDouble -> BORDER_DOUBLE = 0x6
* -4118 = xlDot -> BORDER_HAIR = 0x7
* -4115 = xlDash -> BORDER_DASHED = 0x3
* 1 = xlContinuous
* 4 = xlDashDot -> BORDER_DASH_DOT = 0x9
* 5 = xlDashDotDot -> BORDER_DASH_DOT_DOT = 0xB
* 13 = xlSlantDashDot -> BORDER_SLANTED_DASH_DOT = 0xD
*/
string borderStyle = this.getFeatureBorderStyle(cell.Borders);
/* XlColorIndex
* -4142 = xlColorIndexNone
* -4105 = xlColorIndexAutomatic
*/
double bgColor = cell.Interior.ColorIndex;
int boldFlag = this.getFeatureFontBold(cell.Font);
double height = this.getFeatureFontHeight(cell.Font) * 20.0;
int italicFlag = this.getFeatureFontItalic(cell.Font);
// XlUnderlineStyle
int underlineFlag = this.getFeatureFontUnderline(cell.Font);
DataType dataType = this.getDataType(cell.NumberFormat as string, cellValue);
mSheet.InsertCell(row, col, cType, indents, alignStyle, borderStyle,
(int)bgColor, boldFlag, (int)height, italicFlag, underlineFlag, cellValue);
typeTable[row - upRow, col - leftCol] = dataType;
}
}
DataType[] columnTypes = new DataType[colNum];
this.findColumnType(typeTable, rowNum, colNum, columnTypes);
mSheet.SetColumnTypeTable(columnTypes, colNum);
yield return(new Tuple <string, MSheet>(sheetName, mSheet));
}
}
}
public static List <Tuple <int, int, int, int> > SplitTable(Worksheet sheet, int threshold = 2)
{
List <Tuple <int, int, int, int> > ret = new List <Tuple <int, int, int, int> >();
int rowNum = sheet.UsedRange.Cells.Rows.Count;
int colNum = sheet.UsedRange.Cells.Columns.Count;
int stRow = sheet.UsedRange.Row;
int stCol = sheet.UsedRange.Column;
int rangeUpRow = stRow;
int rangeLeftCol = stCol;
int rangeDownRow = stRow + rowNum - 1;
int rangeRightCol = stCol + colNum - 1;
bool[,] vis = new bool[rowNum, colNum];
int[,] counter = new int[rowNum, colNum];
for (int i = 0; i < rowNum; ++i)
{
for (int j = 0; j < colNum; ++j)
{
counter[i, j] = threshold;
}
}
for (int rowIdx = rangeUpRow; rowIdx <= rangeDownRow; ++rowIdx)
{
for (int colIdx = rangeLeftCol; colIdx <= rangeRightCol; ++colIdx)
{
if (vis[rowIdx - stRow, colIdx - stCol])
{
continue;
}
Range cell = sheet.Cells[rowIdx, colIdx];
string value = Convert.ToString(cell.Value2);
if (value == null || value.Length == 0)
{
continue;
}
Queue <Tuple <int, int> > q = new Queue <Tuple <int, int> >();
q.Enqueue(new Tuple <int, int>(rowIdx, colIdx));
vis[rowIdx - stRow, colIdx - stCol] = true;
int minRow = int.MaxValue;
int minCol = int.MaxValue;
int maxRow = int.MinValue;
int maxCol = int.MinValue;
while (q.Count() > 0)
{
Tuple <int, int> cellCordinate = q.Dequeue();
int row = cellCordinate.Item1;
int col = cellCordinate.Item2;
cell = sheet.Cells[row, col];
if (counter[row - stRow, col - stCol] == 0)
{
continue;
}
if (cell.MergeCells)
{
counter[row - stRow, col - stCol] = threshold;
int upRow = cell.MergeArea.Row;
int leftCol = cell.MergeArea.Column;
int rowCount = cell.MergeArea.Rows.Count;
int colCount = cell.MergeArea.Columns.Count;
for (int j = leftCol; j < leftCol + colCount; ++j)
{
if (!vis[upRow - stRow, j - stCol])
{
q.Enqueue(new Tuple <int, int>(upRow, j));
}
if (!vis[upRow + rowCount - 1 - stRow, j - stCol])
{
q.Enqueue(new Tuple <int, int>(upRow + rowCount - 1, j));
}
}
for (int i = upRow; i < upRow + rowCount; ++i)
{
if (!vis[i - stRow, leftCol - stCol])
{
q.Enqueue(new Tuple <int, int>(i, leftCol));
}
if (!vis[i - stRow, leftCol + colCount - 1 - stCol])
{
q.Enqueue(new Tuple <int, int>(i, leftCol + colCount - 1));
}
}
for (int i = upRow; i < upRow + rowCount; ++i)
{
for (int j = leftCol; j < leftCol + colCount; ++j)
{
vis[i - stRow, j - stCol] = true;
counter[i - stRow, j - stCol] = threshold;
}
}
}
minRow = Math.Min(row, minRow);
minCol = Math.Min(col, minCol);
maxRow = Math.Max(row, maxRow);
maxCol = Math.Max(col, maxCol);
for (int i = 0; i < step; ++i)
{
int nextRow = row + dx[i];
int nextCol = col + dy[i];
if (nextRow >= rangeUpRow && nextRow <= rangeDownRow &&
nextCol >= rangeLeftCol && nextCol <= rangeRightCol &&
!vis[nextRow - stRow, nextCol - stCol])
{
vis[nextRow - stRow, nextCol - stCol] = true;
string cellValue = Convert.ToString(sheet.Cells[nextRow, nextCol].Value2);
if (cellValue == null || cellValue.Length == 0)
{
counter[nextRow - stRow, nextCol - stCol] = counter[row - stRow, col - stCol] - 1;
}
q.Enqueue(new Tuple <int, int>(nextRow, nextCol));
}
}
}
if (minRow == int.MaxValue || minCol == int.MaxValue ||
maxRow == int.MinValue || maxCol == int.MinValue)
{
continue;
}
ret.Add(new Tuple <int, int, int, int>(minRow, minCol, maxRow, maxCol));
}
}
return(TableHelper.Trim(sheet, ret));
}
