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));
}
}
}
// This method is out of date and should not be used!
private Dictionary <string, MSheet> LoadSheetDictByTransposition()
{
Dictionary <string, MSheet> sheetDict = new Dictionary <string, MSheet>();
Sheets sheets = this.workbook.Sheets;
foreach (Worksheet sheet in sheets)
{
// List<Tuple<int, int, int, int>> ranges = TableHelper.SplitTable(sheet);
string sheetName = sheet.Name;
MSheet mSheet = new MSheet();
int rowNum = sheet.UsedRange.Cells.Rows.Count;
int colNum = sheet.UsedRange.Cells.Columns.Count;
int stRow = sheet.UsedRange.Row;
int stCol = sheet.UsedRange.Column;
mSheet.StartRow = stRow;
mSheet.StartCol = stCol;
mSheet.RowNum = rowNum;
mSheet.ColNum = colNum;
bool[,] vis = new bool[rowNum, colNum];
for (int i = 0; i < rowNum; ++i)
{
for (int j = 0; j < colNum; ++j)
{
vis[i, j] = false;
}
}
for (int row = stRow; row < stRow + rowNum; ++row)
{
for (int col = stCol; col < stCol + colNum; ++col)
{
Range cell = sheet.Cells[row, col];
if (vis[row - stRow, col - stCol])
{
continue;
}
if (cell.MergeCells)
{
int rowCount = cell.MergeArea.Rows.Count;
int colCount = cell.MergeArea.Columns.Count;
mSheet.AddMergeCell(col, col + colCount - 1, row, row + rowCount - 1);
for (int i = row; i < row + rowCount; ++i)
{
for (int j = col; j < col + colCount; ++j)
{
vis[i - stRow, j - stCol] = true;
}
}
}
string cellValue = Convert.ToString(cell.Value2);
string cellType = (cell.NumberFormat as string);
if (cellValue == null || cellValue.Length == 0)
{
continue;
}
/*
* 0.00 means precision is 2
* #,## means to use , delimiter
*/
string cType = this.getValueType(cellValue);
string cStr = 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);
mSheet.InsertCell(col, row, cType, indents, alignStyle, borderStyle,
(int)bgColor, boldFlag, (int)height, italicFlag, underlineFlag, cStr);
}
}
sheetDict.Add(sheetName, mSheet);
}
return(sheetDict);
}
