使用Open XML导出到Excel。但最后一个单元格没有填满
问题描述:
我使用下面链接中的代码使用Open XML将数据导出到Excel。使用Open XML导出到Excel。但最后一个单元格没有填满
http://openxmldeveloper.org/forums/permalink/123327/8373/ShowThread.aspx#8373
的代码是在VB.Net所以它转换为C#。它工作正常只有一个(烦人的)故障。最后一个单元(Z5)是空白的。当我使用vb.net版本的代码填充所有的单元格时。我手动比较了C#和VB.Net代码,但是它的功能类似于逐字节的字节。但它仍然无法填满最后一个单元格。任何想法为什么?
C#代码的版本,下面是供你参考:
protected void Export()
{
Response.ClearHeaders();
Response.ClearContent();
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
//"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" '"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" '"application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment; filename=Test.xlsx");
Response.Charset = "";
this.EnableViewState = false;
MemoryStream ms = new MemoryStream();
SpreadsheetDocument objSpreadsheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart objWorkbookPart = objSpreadsheet.AddWorkbookPart();
objWorkbookPart.Workbook = new Workbook();
WorksheetPart objSheetPart = objWorkbookPart.AddNewPart<WorksheetPart>();
objSheetPart.Worksheet = new Worksheet(new SheetData());
Sheets objSheets = objSpreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Sheet objSheet = new Sheet();
objSheet.Id = objSpreadsheet.WorkbookPart.GetIdOfPart(objSheetPart);
objSheet.SheetId = 1;
objSheet.Name = "mySheet";
objSheets.Append(objSheet);
for (int intRow = (int)('A'); intRow <= (int)('Z'); intRow++)
{
for (uint intCol = 1; intCol <= 5; intCol++)
{
Cell objCell = InsertCellInWorksheet(Convert.ToString((char)intRow), intCol, objSheetPart);
objCell.CellValue = new CellValue("This was a test: " + Convert.ToString((char)intRow) + intCol.ToString());
objCell.DataType = new EnumValue<CellValues>(CellValues.String);
objSpreadsheet.WorkbookPart.Workbook.Save();
}
}
objSpreadsheet.WorkbookPart.Workbook.Save();
objSpreadsheet.Close();
ms.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
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);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = row.Elements<Cell>().FirstOrDefault(cell => string.Compare(cell.CellReference.Value, cellReference, true) > 0);
var newCell = new Cell { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
答
问题已解决。 o使代码正常工作。而不是将该工作表保存在InsertCellInWorksheet方法中。我将表格保存在for循环之外。这是代码的工作版本。
protected void Export()
{
Response.ClearHeaders();
Response.ClearContent();
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
//"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" '"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" '"application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment; filename=Test.xlsx");
Response.Charset = "";
this.EnableViewState = false;
MemoryStream ms = new MemoryStream();
SpreadsheetDocument objSpreadsheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart objWorkbookPart = objSpreadsheet.AddWorkbookPart();
objWorkbookPart.Workbook = new Workbook();
WorksheetPart objSheetPart = objWorkbookPart.AddNewPart<WorksheetPart>();
objSheetPart.Worksheet = new Worksheet(new SheetData());
Sheets objSheets = objSpreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Sheet objSheet = new Sheet();
objSheet.Id = objSpreadsheet.WorkbookPart.GetIdOfPart(objSheetPart);
objSheet.SheetId = 1;
objSheet.Name = "mySheet";
objSheets.Append(objSheet);
for (int intRow = (int)('A'); intRow <= (int)('Z'); intRow++)
{
for (uint intCol = 1; intCol <= 5; intCol++)
{
Cell objCell = InsertCellInWorksheet(Convert.ToString((char)intRow), intCol, objSheetPart);
objCell.CellValue = new CellValue("This was a test: " + Convert.ToString((char)intRow) + intCol.ToString());
objCell.DataType = new EnumValue<CellValues>(CellValues.String);
}
}
objSheetPart.Worksheet.Save();
objSpreadsheet.WorkbookPart.Workbook.Save();
objSpreadsheet.Close();
ms.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
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);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = row.Elements<Cell>().FirstOrDefault(cell => string.Compare(cell.CellReference.Value, cellReference, true) > 0);
var newCell = new Cell { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
//worksheet.Save();
return newCell;
}
不知道它在VB.Net如何工作:-)
答
的环通,你必须看起来不正确的行和列。它看起来像循环行= 65(即(int)('A')到行= 65 + 26(即(int)('Z')),然后col = 1到3. 也许你应该循环从col = 1到5.
我认为你可能需要看看你正在处理的xml,并确保你正确地浏览文档(行和列)。如果你不知道这一切office 2007和更高版本的文档都是openXML文档,基本上都是包含xml的zip文件,如果您将电子表格和扩展名从.xlsx更改为.zip,则可以将该文档作为zip文件打开并浏览内容。我一直没有使用openXml一年多,但如果我的记忆正确地为我服务,你会发现代表工作表文件夹中的表格的xml。
感谢响应。现在这个问题已经解决了。请参阅我的回答以获取答案。 – Sajid 2011-03-04 06:54:23