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