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); }