EPPlus Excel的行高不相符 [英] EPPlus Excel Row Height not consistent
本文介绍了EPPlus Excel的行高不相符的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我一直在使用EPPlus生成excel文件，一切似乎是完美的，在MS Office 2007的，但客户端使用MS Office的二千零一十三分之二千零一十有行高是不是29日行后设置。
I have generated an excel file using EPPlus and everything seems to be perfect in MS Office 2007 but the client is using MS Office 2010/2013 and there the Row height is not set after the 29th Row.
这是一个非常奇怪的问题，我一直在尝试了4天这样做，但我不能修复它。
It is a very weird issue and I have been trying to do this for 4 days but am not able to fix it.
我重视从Excel的一个问题截图
I have attached an issue screenshot from excel.
List<spGetInventoryPrintForLabel> inventories = coreInventory.GetInventoryListForLabel(inventoryIDs);
string fileName = Path.Combine(Server.MapPath("~/ExcelLabelDocuments"), "Skyltar " + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx");
ExcelPackage pack = new ExcelPackage();
ExcelWorksheet ws = pack.Workbook.Worksheets.Add("Blad1");
ws.PrinterSettings.PaperSize = ePaperSize.A4;
ws.Cells["A:XFD"].Style.Font.Name = "Arial";
ws.Cells["A:XFD"].Style.Font.Size = 10;
ws.DefaultRowHeight = 16.5;
ws.View.ShowGridLines = false;
ws.Column(1).Width = ws.Column(3).Width = ws.Column(4).Width = ws.Column(6).Width = 4;
ws.Column(2).Width = ws.Column(5).Width = 34.5;
int sizeRowOfLabel = 10;
int recordCount = 0;
int labelStartIndex, rowIndex;
labelStartIndex = rowIndex = 2;
string column = "B";
using (System.Drawing.Image img = System.Drawing.Image.FromFile(Server.MapPath("~/Images/PrintLogo.jpg")))
{
foreach (spGetInventoryPrintForLabel rw in inventories)
{
if (recordCount % 2 == 0)
{
column = "B";
ws.Cells["A" + labelStartIndex + ":C" + labelStartIndex].Style.Border.Top.Style = ExcelBorderStyle.Dashed;
ws.Cells["C" + labelStartIndex + ":C" + (sizeRowOfLabel + labelStartIndex - 1)].Style.Border.Right.Style = ExcelBorderStyle.Dashed;
ws.Cells["A" + (sizeRowOfLabel + labelStartIndex - 1) + ":C" + (sizeRowOfLabel + labelStartIndex - 1)].Style.Border.Bottom.Style = ExcelBorderStyle.Dashed;
rowIndex = labelStartIndex;
if ((sizeRowOfLabel + labelStartIndex - 1) % 41 == 0)
{
ws.Row(sizeRowOfLabel + labelStartIndex - 1).PageBreak = true;
}
}
else
{
column = "E";
ws.Cells["D" + labelStartIndex + ":F" + labelStartIndex].Style.Border.Top.Style = ExcelBorderStyle.Dashed;
ws.Cells["F" + labelStartIndex + ":F" + (sizeRowOfLabel + labelStartIndex - 1)].Style.Border.Right.Style = ExcelBorderStyle.Dashed;
ws.Cells["D" + (sizeRowOfLabel + labelStartIndex - 1) + ":F" + (sizeRowOfLabel + labelStartIndex - 1)].Style.Border.Bottom.Style = ExcelBorderStyle.Dashed;
rowIndex = labelStartIndex;
labelStartIndex += sizeRowOfLabel;
}
//blank
rowIndex++;
ws.Cells[column + rowIndex].Style.Font.Bold = true;
//Artist
ws.Cells[column + rowIndex].Value = rw.ArtistName.Trim() == "," ? "" : rw.ArtistName; rowIndex++;
//Artist year (f. BirthYear) OR (BirthYear - DeathYear)
string artistBirth = string.Empty;
if (string.IsNullOrEmpty(rw.ArtistDeath))
artistBirth = (string.IsNullOrEmpty(rw.ArtistBirth) ? "" : "f. " + rw.ArtistBirth);
else
artistBirth = rw.ArtistBirth + " - " + rw.ArtistDeath;
ws.Cells[column + rowIndex].Value = artistBirth; rowIndex++;
//blank
rowIndex++;
//Art title
ws.Cells[column + rowIndex + ":" + column + (rowIndex + 1)].Merge = true;
ws.Cells[column + rowIndex].Style.Font.Bold = true;
ws.Cells[column + rowIndex].Style.VerticalAlignment = ExcelVerticalAlignment.Top;
ws.Cells[column + rowIndex].Style.WrapText = true;
ws.Cells[column + rowIndex].Value = rw.ArtTitle + (string.IsNullOrEmpty(rw.PurchaseYear) ? "" : ", " + rw.PurchaseYear); rowIndex++;
//blank
rowIndex++;
//Category
string category = string.Empty;
if (!string.IsNullOrEmpty(rw.ArtTechnologyCategory))
{
category = rw.ArtTechnologyCategory;
category += (string.IsNullOrEmpty(rw.ArtSubCategory1)) ? "" : ", " + rw.ArtSubCategory1;
category += (string.IsNullOrEmpty(rw.ArtSubCategory2)) ? "" : ", " + rw.ArtSubCategory2;
}
else
{
category = (string.IsNullOrEmpty(rw.ArtSubCategory1)) ? "" : rw.ArtSubCategory1;
category += (string.IsNullOrEmpty(rw.ArtSubCategory2)) ? "" : ", " + rw.ArtSubCategory2;
}
ws.Cells[column + rowIndex].Value = category; rowIndex++;
//SK Number
ws.Cells[column + rowIndex + ":" + column + (rowIndex + 1)].Merge = true;
ws.Cells[column + rowIndex].Style.VerticalAlignment = ExcelVerticalAlignment.Top;
ws.Cells[column + rowIndex].Value = rw.SKNumber; //rowIndex++;
//Logo
//ws.Cells[column + rowIndex + ":" + column + (rowIndex + 1)].Merge = true;
ExcelPicture pic = ws.Drawings.AddPicture(rw.SKNumber, img);
pic.From.Column = column == "B" ? 1 : 4;
pic.From.Row = rowIndex - 1;
pic.From.ColumnOff = 160 * 9525;
pic.From.RowOff = 4 * 9525;
pic.SetSize(img.Width, img.Height);
recordCount++;
}
}
推荐答案
如果你做的东西像这样的：
What if you do something like this:
for (var i = 1; i <= 110; i++)
{
ws.Row(i).Height = 20;
}
我扔在一个快速的单元测试你的代码，它似乎做的伎俩
I threw your code in a quick unit test and it seemed to do the trick:
[TestMethod]
public void RowHeight_Test()
{
var inventories = new List<RowObject>();
for (var i = 0; i < 20; i++)
{
inventories.Add(new RowObject
{
ArtistName = "DOB - " + i,
ArtSubCategory1 = "Cat 1 - " + i,
ArtSubCategory2 = "Cat 2 - " + i,
ArtTechnologyCategory = "Tech Cat - " + i,
ArtTitle = "Title - " + i,
ArtistBirth = "DOB - " + i,
ArtistDeath = "DOD - " + i,
PurchaseYear = "Year Purchased - " + i,
SKNumber = "SKU Number - " + i,
});
}
string fileName = "c:/temp/temp.xlsx";
var newFile = new FileInfo(fileName);
if (newFile.Exists)
newFile.Delete();
using (ExcelPackage pack = new ExcelPackage(newFile))
{
ExcelWorksheet ws = pack.Workbook.Worksheets.Add("Blad1");
ws.PrinterSettings.PaperSize = ePaperSize.A4;
ws.Cells["A:XFD"].Style.Font.Name = "Arial";
ws.Cells["A:XFD"].Style.Font.Size = 10;
ws.DefaultRowHeight = 16.5;
ws.View.ShowGridLines = false;
ws.Column(1).Width = ws.Column(3).Width = ws.Column(4).Width = ws.Column(6).Width = 4;
ws.Column(2).Width = ws.Column(5).Width = 34.5;
int sizeRowOfLabel = 10;
int recordCount = 0;
int labelStartIndex, rowIndex;
labelStartIndex = rowIndex = 2;
string column = "B";
using (System.Drawing.Image img = System.Drawing.Image.FromFile("c:/temp/logo.png"))
{
foreach (RowObject rw in inventories)
{
if (recordCount%2 == 0)
{
column = "B";
ws.Cells["A" + labelStartIndex + ":C" + labelStartIndex].Style.Border.Top.Style =ExcelBorderStyle.Dashed;
ws.Cells["C" + labelStartIndex + ":C" + (sizeRowOfLabel + labelStartIndex - 1)].Style.Border.Right.Style = ExcelBorderStyle.Dashed;
ws.Cells[
"A" + (sizeRowOfLabel + labelStartIndex - 1) + ":C" +
(sizeRowOfLabel + labelStartIndex - 1)].Style.Border.Bottom.Style =
ExcelBorderStyle.Dashed;
rowIndex = labelStartIndex;
if ((sizeRowOfLabel + labelStartIndex - 1)%41 == 0)
{
ws.Row(sizeRowOfLabel + labelStartIndex - 1).PageBreak = true;
}
}
else
{
column = "E";
ws.Cells["D" + labelStartIndex + ":F" + labelStartIndex].Style.Border.Top.Style =ExcelBorderStyle.Dashed;
ws.Cells["F" + labelStartIndex + ":F" + (sizeRowOfLabel + labelStartIndex - 1)].Style.Border.Right.Style = ExcelBorderStyle.Dashed;
ws.Cells["D" + (sizeRowOfLabel + labelStartIndex - 1) + ":F" +(sizeRowOfLabel + labelStartIndex - 1)].Style.Border.Bottom.Style =ExcelBorderStyle.Dashed;
rowIndex = labelStartIndex;
labelStartIndex += sizeRowOfLabel;
}
//blank
rowIndex++;
ws.Cells[column + rowIndex].Style.Font.Bold = true;
//Artist
ws.Cells[column + rowIndex].Value = rw.ArtistName.Trim() == "," ? "" : rw.ArtistName;
rowIndex++;
//Artist year (f. BirthYear) OR (BirthYear - DeathYear)
string artistBirth = string.Empty;
if (string.IsNullOrEmpty(rw.ArtistDeath))
artistBirth = (string.IsNullOrEmpty(rw.ArtistBirth) ? "" : "f. " + rw.ArtistBirth);
else
artistBirth = rw.ArtistBirth + " - " + rw.ArtistDeath;
ws.Cells[column + rowIndex].Value = artistBirth;
rowIndex++;
//blank
rowIndex++;
//Art title
ws.Cells[column + rowIndex + ":" + column + (rowIndex + 1)].Merge = true;
ws.Cells[column + rowIndex].Style.Font.Bold = true;
ws.Cells[column + rowIndex].Style.VerticalAlignment = ExcelVerticalAlignment.Top;
ws.Cells[column + rowIndex].Style.WrapText = true;
ws.Cells[column + rowIndex].Value = rw.ArtTitle +
(string.IsNullOrEmpty(rw.PurchaseYear)
? ""
: ", " + rw.PurchaseYear);
rowIndex++;
//blank
rowIndex++;
//Category
string category = string.Empty;
if (!string.IsNullOrEmpty(rw.ArtTechnologyCategory))
{
category = rw.ArtTechnologyCategory;
category += (string.IsNullOrEmpty(rw.ArtSubCategory1)) ? "" : ", " + rw.ArtSubCategory1;
category += (string.IsNullOrEmpty(rw.ArtSubCategory2)) ? "" : ", " + rw.ArtSubCategory2;
}
else
{
category = (string.IsNullOrEmpty(rw.ArtSubCategory1)) ? "" : rw.ArtSubCategory1;
category += (string.IsNullOrEmpty(rw.ArtSubCategory2)) ? "" : ", " + rw.ArtSubCategory2;
}
ws.Cells[column + rowIndex].Value = category;
rowIndex++;
//SK Number
ws.Cells[column + rowIndex + ":" + column + (rowIndex + 1)].Merge = true;
ws.Cells[column + rowIndex].Style.VerticalAlignment = ExcelVerticalAlignment.Top;
ws.Cells[column + rowIndex].Value = rw.SKNumber; //rowIndex++;
//Logo
//ws.Cells[column + rowIndex + ":" + column + (rowIndex + 1)].Merge = true;
ExcelPicture pic = ws.Drawings.AddPicture(rw.SKNumber, img);
pic.From.Column = column == "B" ? 1 : 4;
pic.From.Row = rowIndex - 1;
pic.From.ColumnOff = 160*9525;
pic.From.RowOff = 4*9525;
pic.SetSize(img.Width, img.Height);
recordCount++;
}
}
for (var i = 1; i <= 110; i++)
{
ws.Row(i).Height = 20;
}
pack.Save();
}
}
这篇关于EPPlus Excel的行高不相符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
