Excel sheet cut off trying to export

Question

Using c#, npoi

Basically I used npoi to create and export data to an excel sheet but I'm open to other ways other than npoi to fix it. The data still exports as intended but when I try to print using excel, the sheet looks like below. I want to fix it directly in the code so what went wrong?

    dataRow = (HSSFRow)sheet.CreateRow(31);
    dataRow.CreateCell(0).SetCellValue("Name");
    dataRow.GetCell(0).CellStyle = fontmaintoprightbotleft;  
    for (int i = 1; i < 14; i++)                              //this only puts the border on top of the undeclared cells
    {
        dataRow.CreateCell(i).CellStyle = fontmaintoprightbotleft;
    }
    cra = new NPOI.SS.Util.CellRangeAddress(31, 31, 0, 5);
    sheet.AddMergedRegion(cra);
    dataRow.CreateCell(6).SetCellValue("Contact No. (Hp)");
    dataRow.GetCell(6).CellStyle = fontmaintoprightbotleft;
    cra = new NPOI.SS.Util.CellRangeAddress(31, 31, 6, 8);
    sheet.AddMergedRegion(cra);
    dataRow.CreateCell(9).SetCellValue("Relationship");
    dataRow.GetCell(9).CellStyle = fontmaintoprightbotleft;
    cra = new NPOI.SS.Util.CellRangeAddress(31, 31, 9, 13);
    sheet.AddMergedRegion(cra);

    dataRow = (HSSFRow)sheet.CreateRow(32);
    dataRow.CreateCell(0).SetCellValue(EmergNametxt.Text);
    dataRow.GetCell(0).CellStyle = fontmaintoprightbotleft;            //this only puts the border on top of the undeclared cells
    for (int i = 1; i < 14; i++)
    {
        dataRow.CreateCell(i).CellStyle = fontmaintoprightbotleft;
    }
    cra = new NPOI.SS.Util.CellRangeAddress(32, 32, 0, 5);
    sheet.AddMergedRegion(cra);
    dataRow.CreateCell(6).SetCellValue(EmergContactNoTextBox.Text);
    dataRow.GetCell(6).CellStyle = fontmaintoprightbotleft;
    cra = new NPOI.SS.Util.CellRangeAddress(32, 32, 6, 8);
    sheet.AddMergedRegion(cra);
    dataRow.CreateCell(9).SetCellValue(EmergRelationshiptxt.Text);
    dataRow.GetCell(9).CellStyle = fontmaintoprightbotleft;
    cra = new NPOI.SS.Util.CellRangeAddress(32, 32, 9, 13);
    sheet.AddMergedRegion(cra);


    dataRow = (HSSFRow)sheet.CreateRow(33);
    dataRow.CreateCell(0).SetCellValue("Education Profile");
    for (int i = 1; i < 14; i++)
    {
        dataRow.CreateCell(i).CellStyle = fontheader;
    }
    dataRow.GetCell(0).CellStyle = fontheader;
    cra = new NPOI.SS.Util.CellRangeAddress(33, 33, 0, 13);
    sheet.AddMergedRegion(cra);

    dataRow = (HSSFRow)sheet.CreateRow(34);

I don't know enough about npoi to provide an answer but this looks like normal Excel printing behavior to me -- You may need to either adjust column widths to fit on one page or set the "fit to page" or "fit to width" printing property. — rexypoo, Jan 29 '20 at 05:05
Yea but unfortunately, npoi doesn't have a method to break page or fit. — timetoquitcoding, Jan 29 '20 at 06:02
Hey, it might be helpful to provide the code that can reproduce this circumstance, if applicable. — CausingUnderflowsEverywhere, Jan 29 '20 at 07:13
I added in the code but its pretty much just repeated making of rows and columns. — timetoquitcoding, Jan 29 '20 at 07:45

score 0 · Answer 1 · answered Feb 03 '20 at 05:58

0

The documentation for the C# NPOI implementation seems to be poor, but I think you should have the POI methods for print settings

sheet.FitToPage = true;

There's another StackOverflow answer that suggests the above setting may override the following PrintSetup items:

sheet.PrintSetup.FitHeight = 1;
sheet.PrintSetup.FitWidth = 1;

answered Feb 03 '20 at 05:58

rexypoo

1,697
1
6
10

The sheet.PrintSetup will fit the whole form into 1 page but mine is about 2 pages back to back so they just make everything really small. I'm not sure what the sheet.FitToPage changed when I tried implementing it. – timetoquitcoding Feb 03 '20 at 06:48
1

@timetoquitcoding In that case I think you'll need to update the question with details about how you'd like it to print. I can take a look if you can provide more details. – rexypoo Feb 04 '20 at 02:39
Doubt there's an answer but I want both pictures to be on the same page because, as of now, it's separated to different pages because the rows are too long and thus excel is cutting it off. Thanks for helping though. – timetoquitcoding Feb 04 '20 at 09:10

Excel sheet cut off trying to export

1 Answers1