在某些列之后，使用Open Xml写入Excel会出现问题 [英] Writing to Excel with Open Xml gives issue after certain columns
本文介绍了在某些列之后，使用Open Xml写入Excel会出现问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我将C#与Open XML Format SDK 2.0结合使用，试图编写Excel报告文件.我的问题是，当我越过Z列(到达AA)时，文件已损坏并且无法由用户手动打开.有什么想法吗?
I am using C# with Open XML Format SDK 2.0, Trying to write an Excel report file. My problem is when I cross the Z column (reaching AA) the file goes corrupted and cannot be open manually by the user. Any ideas?
static void Main(string[] args)
{
PortfolioReport report = new PortfolioReport("Keywords");
report.CreateReport();
}
public PortfolioReport(string client)
{
string newFileName = path + client + ".xlsx";
if (File.Exists(newFileName))
{
File.Delete(newFileName);
}
FileInfo newFile = new FileInfo(newFileName);
using (ExcelPackage package = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Keywords");
package.Save();
}
document = SpreadsheetDocument.Open(newFileName, true);
wbPart = document.WorkbookPart;
}
static readonly string[] Columns = new[] { "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", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH" };
public static string IndexToColumn(int index)
{
if (index < 0)
throw new IndexOutOfRangeException("index must be a positive number");
return Columns[index];
}
public static object GetPropValue(object src, string propName)
{
return src.GetType().GetProperty(propName).GetValue(src, null);
}
// Create a new Portfolio report
public void CreateReport()
{
string wsName = "Report Summary";
wbPart.Workbook.Descendants<Sheet>().FirstOrDefault().Name = wsName;
var currentUser = UsersInfo.Keywords;
//set the domainAge
KeywordsModule.SetYearsAndMonths(currentUser.Keywords);
//set the url site relevency in words
KeywordsModule.SetAverageUrlSiteRelevencyLiteral(currentUser.Keywords);
//set a model for the excel which will convert the keyword to the custom model according to gui names
List<KeywordModelForExcelExport> keywordsForExports = KeywordsModule.PrepreKeywordSforExport(currentUser.Keywords);
//we set the column headings
var properties = typeof(KeywordModelForExcelExport).GetProperties();
for (int i = 0; i < properties.Length; i++)
{
var cell = IndexToColumn(i) + 1;
UpdateValue(wsName, cell, properties[i].Name, 0, true);
}
//now we set the keyword values
int row = 2;
foreach (var keywordForExport in keywordsForExports)
{
for (int i = 0; i < properties.Length; i++)
{
var val = GetPropValue(keywordForExport, properties[i].Name);
var cell = IndexToColumn(i) + row;
if (val != null)
UpdateValue(wsName, cell, val.ToString(), 0, true);
}
row++;
}
// All done! Close and save the document.
document.Close();
}
// Given a Worksheet and an address (like "AZ254"), either return a cell reference, or
// create the cell reference and return it.
private Cell InsertCellInWorksheet(Worksheet ws, string addressName)
{
SheetData sheetData = ws.GetFirstChild<SheetData>();
Cell cell = null;
UInt32 rowNumber = GetRowIndex(addressName);
Row row = GetRow(sheetData, rowNumber);
// If the cell you need already exists, return it.
// If there is not a cell with the specified column name, insert one.
Cell refCell = row.Elements<Cell>().
Where(c => c.CellReference.Value == addressName).FirstOrDefault();
if (refCell != null)
{
cell = refCell;
}
else
{
cell = CreateCell(row, addressName);
}
return cell;
}
private Cell CreateCell(Row row, String address)
{
Cell cellResult;
Cell refCell = null;
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, address, true) > 0)
{
refCell = cell;
break;
}
}
cellResult = new Cell();
cellResult.CellReference = address;
row.InsertBefore(cellResult, refCell);
return cellResult;
}
private Row GetRow(SheetData wsData, UInt32 rowIndex)
{
var row = wsData.Elements<Row>().
Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
if (row == null)
{
row = new Row();
row.RowIndex = rowIndex;
wsData.Append(row);
}
return row;
}
private UInt32 GetRowIndex(string address)
{
string rowPart;
UInt32 l;
UInt32 result = 0;
for (int i = 0; i < address.Length; i++)
{
if (UInt32.TryParse(address.Substring(i, 1), out l))
{
rowPart = address.Substring(i, address.Length - i);
if (UInt32.TryParse(rowPart, out l))
{
result = l;
break;
}
}
}
return result;
}
public bool UpdateValue(string sheetName, string addressName, string value, UInt32Value styleIndex, bool isString)
{
// Assume failure.
bool updated = false;
Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();
if (sheet != null)
{
Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
Cell cell = InsertCellInWorksheet(ws, addressName);
if (isString)
{
// Either retrieve the index of an existing string,
// or insert the string into the shared string table
// and get the index of the new item.
int stringIndex = InsertSharedStringItem(wbPart, value);
cell.CellValue = new CellValue(stringIndex.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
else
{
cell.CellValue = new CellValue(value);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
if (styleIndex > 0)
cell.StyleIndex = styleIndex;
// Save the worksheet.
ws.Save();
updated = true;
}
return updated;
}
// Given the main workbook part, and a text value, insert the text into the shared
// string table. Create the table if necessary. If the value already exists, return
// its index. If it doesn't exist, insert it and return its new index.
private int InsertSharedStringItem(WorkbookPart wbPart, string value)
{
int index = 0;
bool found = false;
var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something's wrong.
// Just return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (stringTablePart == null)
{
// Create it.
stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
stringTablePart.SharedStringTable = new SharedStringTable();
}
var stringTable = stringTablePart.SharedStringTable;
// if (stringTable == null)
// {
// stringTable = new SharedStringTable();
// }
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == value)
{
found = true;
break;
}
index += 1;
}
if (!found)
{
stringTable.AppendChild(new SharedStringItem(new Text(value)));
stringTable.Save();
}
return index;
}
推荐答案
对于 Z 之后的单元格引用，您的顺序在 CreateCell 中被破坏.您当前正在使用 string.Compare ，但这将进行alpha比较，这意味着 AA1 在之前 Z1 它.
Your ordering is broken in CreateCell for cell references after Z. You are currently using string.Compare but that will do an alpha comparison meaning AA1 is before Z1 rather than after it.
有多种方法可以解决此问题-一种方法是将 cellReference 转换为列索引，然后进行比较而不是直接比较单元格引用.例如:
There are various ways you could fix this - one way would be to convert the cellReference to a column index and then compare those instead of comparing the cell references directly. For example:
private static int? GetColumnIndex(string cellRef)
{
if (string.IsNullOrEmpty(cellRef))
return null;
cellRef = cellRef.ToUpper();
int columnIndex = -1;
int mulitplier = 1;
foreach (char c in cellRef.ToCharArray().Reverse())
{
if (char.IsLetter(c))
{
columnIndex += mulitplier * ((int)c - 64);
mulitplier = mulitplier * 26;
}
}
return columnIndex;
}
然后代替
if (string.Compare(cell.CellReference.Value, address, true) > 0)
{
refCell = cell;
break;
}
你可以做
if (GetColumnIndex(cell.CellReference.Value) > GetColumnIndex(address))
{
refCell = cell;
break;
}
这篇关于在某些列之后，使用Open Xml写入Excel会出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
