public void RepeatingRowsAndColumns(int Sheetindex, int RowIdex, int CellIndex)
{
InitializeWorkbook();
ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex);
IFont boldFont = hssfworkbook.CreateFont();
boldFont.FontHeightInPoints = 22;
boldFont.Boldweight = (short)FontBoldWeight.BOLD;
ICellStyle boldStyle = hssfworkbook.CreateCellStyle();
boldStyle.SetFont(boldFont);
IRow row = sheet1.GetRow(RowIdex);
ICell cell = row.GetCell(CellIndex);
cell.CellStyle = (boldStyle);
// Set the columns to repeat from column 0 to 2 on the first sheet
hssfworkbook.SetRepeatingRowsAndColumns(Sheetindex, 0, 2, -1, -1);
WriteToFile();
}
public void TestFindBuiltInNameRecord()
{
// TestRRaC has multiple (3) built-in name records
// The second print titles name record has SheetNumber==4
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRRaC.xls");
NameRecord nr;
Assert.AreEqual(3, wb.Workbook.NumNames);
nr = wb.Workbook.GetNameRecord(2);
// TODO - render full row and full column refs properly
Assert.AreEqual("Sheet2!$A$1:$IV$1", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // 1:1
try
{
wb.SetRepeatingRowsAndColumns(3, 4, 5, 8, 11);
}
catch (Exception e)
{
if (e.Message.Equals("Builtin (7) already exists for sheet (4)"))
{
// there was a problem in the code which locates the existing print titles name record
throw new Exception("Identified bug 45720b");
}
throw e;
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(3, wb.Workbook.NumNames);
nr = wb.Workbook.GetNameRecord(2);
Assert.AreEqual("Sheet2!E:F,Sheet2!$A$9:$IV$12", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // E:F,9:12
}
public void TestRepeatingRowsAndColumsNames()
{
// First Test that Setting RR&C for same sheet more than once only Creates a
// single Print_Titles built-in record
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("FirstSheet");
// set repeating rows and columns twice for the first sheet
for (int i = 0; i < 2; i++)
{
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3 - 1);
sheet.CreateFreezePane(0, 3);
}
Assert.AreEqual(1, wb.NumberOfNames);
IName nr1 = wb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
if (false)
{
#if !HIDE_UNREACHABLE_CODE
// TODO - full column references not rendering properly, absolute markers not present either
Assert.AreEqual("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.RefersToFormula);
#endif
}
else
{
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
}
// Save and re-open
HSSFWorkbook nwb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(1, nwb.NumberOfNames);
nr1 = nwb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
// check that Setting RR&C on a second sheet causes a new Print_Titles built-in
// name to be Created
sheet = (HSSFSheet)nwb.CreateSheet("SecondSheet");
nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0);
Assert.AreEqual(2, nwb.NumberOfNames);
IName nr2 = nwb.GetNameAt(1);
Assert.AreEqual("Print_Titles", nr2.NameName);
Assert.AreEqual("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.RefersToFormula);
//if (false) {
// // In case you fancy Checking in excel, to ensure it
// // won't complain about the file now
// File tempFile = File.CreateTempFile("POI-45126-", ".xls");
// FileOutputStream fout = new FileOutputStream(tempFile);
// nwb.Write(fout);
// fout.close();
// Console.WriteLine("check out " + tempFile.GetAbsolutePath());
//}
}
public void TestSetRepeatingRowsAndColumns()
{
// Test bug 29747
HSSFWorkbook b = new HSSFWorkbook();
b.CreateSheet();
b.CreateSheet();
b.CreateSheet();
b.SetRepeatingRowsAndColumns(2, 0, 1, -1, -1);
NameRecord nameRecord = b.Workbook.GetNameRecord(0);
Assert.AreEqual(3, nameRecord.SheetNumber);
}
public void TestRepeatingColsRowsMinusOne()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Test Print Titles");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.SetCellValue(new HSSFRichTextString("hi"));
workbook.SetRepeatingRowsAndColumns(0, -1, 1, -1, 0);
FileInfo file = TempFile.CreateTempFile("testPrintTitlesA", ".xls");
FileStream fileOut = new FileStream(file.FullName, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(fileOut);
fileOut.Close();
Assert.IsTrue(file.Exists, "file exists");
}
public void TestRepeatingColsRows()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Test Print Titles");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.SetCellValue(new HSSFRichTextString("hi"));
workbook.SetRepeatingRowsAndColumns(0, 0, 1, 0, 0);
string filepath = TempFile.GetTempFilePath("TestPrintTitles", ".xls");
FileStream fileOut = new FileStream(filepath, FileMode.OpenOrCreate);
workbook.Write(fileOut);
fileOut.Close();
Assert.IsTrue(File.Exists(filepath), "file exists");
}
public void TestRepeatingRowsAndColumsNames()
{
// First Test that setting RR&C for same sheet more than once only Creates a
// single Print_Titles built-in record
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("FirstSheet");
// set repeating rows and columns twice for the first sheet
for (int i = 0; i < 2; i++)
{
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3 - 1);
sheet.CreateFreezePane(0, 3);
}
Assert.AreEqual(1, wb.NumberOfNames);
NPOI.SS.UserModel.Name nr1 = wb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
if (false)
{
// TODO - full column references not rendering properly, absolute markers not present either
Assert.AreEqual("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.RefersToFormula);
}
else
{
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
}
// Save and re-Open
HSSFWorkbook nwb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(1, nwb.NumberOfNames);
nr1 = nwb.GetNameAt(0);
Assert.AreEqual("Print_Titles", nr1.NameName);
Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
// Check that setting RR&C on a second sheet causes a new Print_Titles built-in
// name to be Created
sheet = nwb.CreateSheet("SecondSheet");
nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0);
Assert.AreEqual(2, nwb.NumberOfNames);
NPOI.SS.UserModel.Name nr2 = nwb.GetNameAt(1);
Assert.AreEqual("Print_Titles", nr2.NameName);
Assert.AreEqual("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.RefersToFormula);
if (false)
{
// In case you fancy Checking in excel, to ensure it
// won't complain about the file now
try
{
string tmppath = NPOI.Util.TempFile.GetTempFilePath("POI-45126-", ".xls");
FileStream fout = new FileStream(tmppath, FileMode.OpenOrCreate);
nwb.Write(fout);
fout.Close();
Console.WriteLine("Check out " + Path.GetFullPath(tmppath));
}
catch (IOException)
{
throw;
}
}
}
/// <summary>
/// 填充数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="records"></param>
/// <param name="root"></param>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="columns"></param>
internal void SetData <T>(IList <T> records, Root root, HSSFWorkbook workbook, ISheet sheet, List <PropertyInfo> columns)
{
IList <HSSFCellStyle> ics = new List <HSSFCellStyle>();
Int32 height = 20;
for (Int32 r = 0; r < records.Count; r++)
{
IRow row = null;
if (!sheet.IsNullOrEmpty())
{
row = sheet.CreateRow(root.head.rowspan.GetInt32() + r);
}
for (Int32 i = 0; i < columns.Count; i++)
{
if (columns[i].IsNullOrEmpty())
{
continue;
}
var value = columns[i].GetValue(records[r], null);
String drValue = String.Empty;
if (value != null)
{
drValue = value.ToString();
}
drValue = drValue.Replace("<br/>", "\n");
drValue = drValue.Replace("<br>", "\n");
if (!row.IsNullOrEmpty())
{
ICell newCell = row.CreateCell(i);
if (ics.Count < columns.Count)
{
ExportColumn ec = root.head.columns.SingleOrDefault(c => c.dataIndex == columns[i].Name);
SetHeadCellBold(ec);
// 设置行高
if (!ec.dheight.IsNullOrEmpty())
{
height = ec.dheight.GetInt32() == 0 ? 1 : ec.dheight.GetInt32();
}
// 获得样式
ICellStyle style = GetCellStyle(workbook, ExcelStyle.Default, ec);
ics.Add(style as HSSFCellStyle);
newCell.CellStyle = style;
//设置单元格的宽度
if (!root.head.defaultwidth.IsNullOrEmpty() && !ec.width.IsNullOrEmpty())
{
sheet.SetColumnWidth(i, ec.width * 40);
}
else
{
sheet.AutoSizeColumn(i); //每列宽度自适应
}
}
else if (ics.Count == columns.Count)
{
HSSFCellStyle style = ics[i];
newCell.CellStyle = style;
}
if (height > 20)
{
row.Height = (short)(height * 20);
}
newCell.SetCellValue(drValue);
}
}
}
// 打印水平居中
sheet.HorizontallyCenter = true;
// 设置打印方向
sheet.PrintSetup.Landscape = root.head.landscape;
// 设置网络线
sheet.DisplayGridlines = false;
// 上边距
sheet.SetMargin(MarginType.TopMargin, root.topMargin.IsNullOrEmpty() ? 1.05 : root.topMargin.GetDouble());
// 下边距
sheet.SetMargin(MarginType.BottomMargin, root.bottomMargin.IsNullOrEmpty() ? 0.7 : root.bottomMargin.GetDouble());
// 左边距
sheet.SetMargin(MarginType.LeftMargin, root.leftMargin.IsNullOrEmpty() ? 0.7 : root.leftMargin.GetDouble());
// 右边距
sheet.SetMargin(MarginType.RightMargin, root.rightMargin.IsNullOrEmpty() ? 0.7 : root.rightMargin.GetDouble());
//// 页眉边距
sheet.PrintSetup.HeaderMargin = root.headerMargin.IsNullOrEmpty() ? 0.5 : root.headerMargin.GetDouble();
//// 页脚边距
sheet.PrintSetup.FooterMargin = root.footerMargin.IsNullOrEmpty() ? 0.5 : root.footerMargin.GetDouble();
// 设置缩放比例
sheet.PrintSetup.FitWidth = 100;
sheet.PrintSetup.FitHeight = 100;
sheet.PrintSetup.Scale = 100;
sheet.SetZoom(1, 1);
// 设置纸张
sheet.PrintSetup.PaperSize = 9;
// 设置顶端标题行
Int32 endRow = root.head.rowspan.GetInt32() > 1 ? root.head.rowspan.GetInt32() - 1 : 0;
workbook.SetRepeatingRowsAndColumns(0, 0, 0, 0, endRow);
}
