您可以实现您想要的方式之一是找出所有行中最大的列索引,然后使用空白填充所有空单元格值。这将使所有列正确对齐。
以下是一个快速代码片段,用于确定最大列索引:
int? biggestColumnIndex = 0;
foreach (Row row in rows)
{
if (row.Descendants<Cell>().Any())
{
int? columnIndex = GetColumnIndexFromName(((Cell)(row.LastChild)).CellReference);
biggestColumnIndex = columnIndex.HasValue && columnIndex > biggestColumnIndex ? columnIndex : biggestColumnIndex;
}
}
public static int? GetColumnIndexFromName(string columnName)
{
int? columnIndex = null;
string[] colLetters = Regex.Split(columnName, "([A-Z]+)");
colLetters = colLetters.Where(s => !string.IsNullOrEmpty(s)).ToArray();
if (colLetters.Count() <= 2)
{
int index = 0;
foreach (string col in colLetters)
{
List<char> col1 = colLetters.ElementAt(index).ToCharArray().ToList();
int? indexValue = Letters.IndexOf(col1.ElementAt(index));
if (indexValue != -1)
{
if (index == 0 && colLetters.Count() == 2)
{
columnIndex = columnIndex == null ? (indexValue + 1) * 26 : columnIndex + ((indexValue + 1) * 26);
}
else
{
columnIndex = columnIndex == null ? indexValue : columnIndex + indexValue;
}
}
index++;
}
}
return columnIndex;
}
在你拥有最大列索引后,调用InsetCellsForCellRange
方法以填充所有空单元格为白色单元格。然后读取你的数据,它们应该全部对齐。(所有辅助方法都在InsetCellsForCellRange
方法下方)
public static void InsertCellsForCellRange(string startCellReference, string endCellReference, WorksheetPart worksheetPart)
{
uint startRow = GetRowIndex(startCellReference);
uint endRow = GetRowIndex(endCellReference);
string startColumn = GetColumnName(startCellReference);
string endColumn = GetColumnName(endCellReference);
for (uint currentRow = startRow; currentRow <= endRow; currentRow++)
{
string currentCell = startColumn + currentRow.ToString();
string endCell = IncrementCellReference(endColumn + currentRow.ToString(), CellReferencePartEnum.Column);
while (!currentCell.Equals(endCell))
{
if (GetCell(worksheetPart, currentCell) == null)
{
InsertCell(GetColumnName(currentCell), GetRowIndex(currentCell), worksheetPart);
}
currentCell = IncrementCellReference(currentCell, CellReferencePartEnum.Column);
}
}
}
public static uint GetRowIndex(string cellReference)
{
Regex regex = new Regex(@"\d+");
Match match = regex.Match(cellReference);
return uint.Parse(match.Value);
}
public static string GetColumnName(string cellReference)
{
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellReference);
return match.Value;
}
public static string IncrementCellReference(string reference, CellReferencePartEnum cellRefPart)
{
string newReference = reference;
if (cellRefPart != CellReferencePartEnum.None && !String.IsNullOrEmpty(reference))
{
string[] parts = Regex.Split(reference, "([A-Z]+)");
if (cellRefPart == CellReferencePartEnum.Column || cellRefPart == CellReferencePartEnum.Both)
{
List<char> col = parts[1].ToCharArray().ToList();
bool needsIncrement = true;
int index = col.Count - 1;
do
{
col[index] = Letters[Letters.IndexOf(col[index]) + 1];
if (col[index] == Letters[Letters.Count - 1])
{
col[index] = Letters[0];
}
else
{
needsIncrement = false;
}
} while (needsIncrement && --index >= 0);
if (needsIncrement)
{
col.Add(Letters[0]);
}
parts[1] = new String(col.ToArray());
}
if (cellRefPart == CellReferencePartEnum.Row || cellRefPart == CellReferencePartEnum.Both)
{
parts[2] = (int.Parse(parts[2]) + 1).ToString();
}
newReference = parts[1] + parts[2];
}
return newReference;
}
public static Cell GetCell(WorksheetPart workSheetPart, string cellAddress)
{
return workSheetPart.Worksheet.Descendants<Cell>()
.Where(c => cellAddress.Equals(c.CellReference))
.SingleOrDefault();
}
public static Cell InsertCell(string colName, uint rowIndex, WorksheetPart worksheetPart)
{
return InsertCell(colName, rowIndex, worksheetPart, null);
}
public static Cell InsertCell(string colName, uint rowIndex, WorksheetPart worksheetPart, Cell insertCell)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string insertReference = colName + rowIndex;
Row row;
if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
Cell retCell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference.Value == colName + rowIndex);
if (retCell != null)
{
if (insertCell != null)
{
List<Cell> cells = row.Descendants<Cell>().Where(c => String.Compare(c.CellReference.Value, insertReference) >= 0).ToList();
cells.Insert(0, insertCell);
string cellReference = insertReference;
foreach (Cell cell in cells)
{
cell.CellReference = new StringValue(cellReference);
IncrementCellReference(cellReference, CellReferencePartEnum.Column);
}
retCell = row.InsertBefore(insertCell, retCell);
}
}
else
{
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
string cellColumn = Regex.Replace(cell.CellReference.Value, @"\d", "");
if (colName.Length <= cellColumn.Length && string.Compare(cell.CellReference.Value, insertReference, true) > 0)
{
refCell = cell;
break;
}
}
retCell = insertCell ?? new Cell() { CellReference = insertReference };
row.InsertBefore(retCell, refCell);
}
return retCell;
}
public enum CellReferencePartEnum
{
None,
Column,
Row,
Both
}
private static List<char> Letters = new List<char>() { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', ' ' };