Method of C winform Printing excel

Preface

c # winform program requires generating and printing Excel report, In order not to install the corresponding components of Office, I chose NPOI to generate Excel report, and used PrintDocument control of winform to trigger printing operation. The difficulty lies in how to convert excel into Graphics object. In NPOI, I only found the printing settings of excel (such as horizontal/vertical), and I need to open excel to trigger printing operation. However, the project requirement is to directly realize printing once, and use PrintDocument control instead of operating excel again. I had to search again and found the class library Spire. xls, which finally realized the requirements. If there are any mistakes or more concise methods, please feel free to comment on them.

Train of thought

Generate Excel with npoi = "Convert to picture with spire. xls. png =" Get the generated picture, use drawimage method of Graphic to operate on the picture, of course, if you don't mind, you can not operate on the picture:) = "Print with print method of printDocument control (of course, you can directly generate Excel with spire. xls here, but the free version of Spire. xls has a limit of 150 lines per sheet)

Knowledge point

How to use "0" NPOI

Download: http://npoi.codeplex.com/releases/

printDocument control for "1" winform

Reference: https://docs.microsoft.com/en-us/dotnet/framework/advanced/advanced/how-to-print-a-multi-page-file-in-forms-forms

Understand:

The function of PrintDocument control is to define an object that sends output to the printer, including BeginPrint, PrintPage and EndPrint methods. When executing printdocument. Print () statement, it will check whether BeginPrint method exists, and call it first if it exists. Then call the PrintPage method, which will get a blank Graphics object from the PrintPageEcentArgs class. We can operate on the Graphics object (it is understandable to draw canvas, and the Graphics object has built-in methods like DrawString and DrawPie). At the end of the PrintPage method, this Graphics object will be sent to the printing device. After the printing device finishes printing, it will check whether the EndPrint method exists, and call it if it exists.

"2" spire. How to use xls

Reference:. NET Reading and Writing Excel Tool Spire. Xls Using Getting Started Tutorial (1)

Specific code

Introduction to Print Controls Used in "1" winform

Screenshot of plug-ins used

printDocument1 Action: Defines an object that sends output to a printer

What printPreviewDialog1 does: Displays a dialog box that shows the user what the associated document looks like when printed

What printDialog1 does: Displays a dialog box that allows the user to select a printer and select other printing options (such as fraction, paper orientation)

"2" code display

Background Settings of "Direct Print" Button

private void DirectPrint_Click(object sender, EventArgs e)
{
isprint = false;
GenerateExcel_Click(sender, e); // Use NPOI Generate excel
if (newsavefilepath != "" && isprint==true)
{
isprint = false; 
ChangeExcel2Image(newsavefilepath); // Utilization Spire Will excel Convert to a picture 
if (printDialog1.ShowDialog() == DialogResult.OK)
{ 
printDocument1.Print(); // Print 
}
} 

}

Background Settings of "Generate excel" Button

private void Generate_Click(object sender, EventArgs e)
{
CreateExcel(); // Use NPOI Generate excel Content 
SaveFileDialog savedialog = new SaveFileDialog(); // Bomb allows users to choose excel Window to save path 
savedialog.Filter = " excel files(*.xlsx)|*.xlsx|All files(*.*)|*.*";
savedialog.RestoreDirectory = true;
savedialog.FileName = string.Format(" Sales Order Approval Form {0}", DateTime.Now.ToString("yyyyMMddHHmm"));
if (savedialog.ShowDialog() == DialogResult.OK)
{
//newsavefilepath Yes excel Save path of 
newsavefilepath = savedialog.FileName.ToString().Trim();
using (FileStream newfs = new FileStream(newsavefilepath, FileMode.Create, FileAccess.ReadWrite))
{
singlexssfwk.Write(newfs); // The that will be generated excel Write to the file path selected by the user to save 
newfs.Close();
}
} 
}

Example of CreateExcel () method

using NPOI.XSSF.UserModel;

XSSFWorkbook singlexssfwk;

// Note that different NPOI The method called by the version does not 1 To, the version used here is 2.1.3.1
private void CreatExcel() 
{ 
// Get a template excel Path of 
string str = System.Environment.CurrentDirectory + "\\XXXX.xlsx";
if (File.Exists(str))
{
using (FileStream fs = new FileStream(str, FileMode.Open, FileAccess.Read))
{
singlexssfwk = new XSSFWorkbook(fs);
fs.Close();
}
// Get table 
XSSFSheet xssfsheet = (XSSFSheet)singlexssfwk.GetSheetAt(0);
// Create a row  
XSSFRow xssfrow1 = (XSSFRow)xssfsheet.GetRow(1);
// Set cell contents 
xssfrow1.GetCell(0).SetCellValue("...");
... ...
 　　　  }
else{
... ...
}
}

Example of ChangeExcel2Image () method

using Spire.Xls; 
public void ChangeExcel2Image(string filename)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(filename);
Worksheet sheet = workbook.Worksheets[0];
sheet.SaveToImage(imagepath); // Picture suffix .bmp ,imagepath Set up by yourself 
}

The method that executes printDocument1.Print () calls the printDocument1_PrintPage method

// In PrintPage Write and intercept pictures in the method   Code of 
private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
{ 
#region  If you don't need to intercept pictures, you don't need to write the following code 
GC.Collect();
Graphics g = e.Graphics; 
//imagepath Refers to  excel The path of the image to be converted 
using (Bitmap bitmap = new dBitmap(imagepath))
{
// How to intercept your own exploration 
Rectangle newarea = new Rectangle();
newarea.X = 0;
newarea.Y = 50;
newarea.Width = bitmap.Width;
newarea.Height = bitmap.Height - 120;
using (Bitmap newbitmap = bitmap.Clone(newarea, bitmap.PixelFormat))
{
g.DrawImage(newbitmap, 0, 0, newbitmap.Width - 200, newbitmap.Height - 150);
}
} 
#endregion
}

Note: Regarding the preview setting, the key is that printDocument1 is a global object, and printPreviewDialog1. Document = printDocument1; printPreviewDialog1. ShowDialog (); Yes, the principle of this has not been studied. Anyway, after this setting, as long as you handle the content well, you can see it in the preview.

Summarize

The above is how I realized c # winform printing excel. Perhaps this is just a relatively jumbled method. If there are bosses who have a more streamlined method, please feel free to comment. If there are any mistakes, please feel free to comment.