C # operations Excel printing

Using System;
Using System.Data;
Using System.IO;
Using System.Runtime.InteropServices;
Using System.Threading;
Using System.Windows.Forms;
Using Microsoft.Office.Core;
Using Excel = Microsoft.Office.Interop.Excel;

Namespace WindowsFormsApplication1
{
public partial class Form1:form
{
private bool Isstartprint = false;

Public Form1 ()
{
InitializeComponent ();
}

private void Button1_Click (object sender, EventArgs e)
{
String OriginalPath = Path.getdirectoryname (application.executablepath) + "\\IMS.accdb";
Initializing the database
DataBase.OleDbObject.InitDatabase (OriginalPath);
DataBase.OleDbObject.OpenDb ();
int K=database.oledbobject.select ("SELECT * from Elementinventory"). Rows.Count;

Initialize the data to be written to Excel
DataTable dt = new DataTable ();
for (int i = 0; i < one; i++)
{
DataColumn dc = new DataColumn ();
dc. ColumnName = "name" + i;
dc. DataType = typeof (String);
Dt. Columns.Add (DC);
}
for (int i = 0; i <; i++)
{
DataRow dr = dt. NewRow ();
if (i% 2 = = 0)
{
DR[0] = "Test Case" + i + "number to start testing for line wrapping, and page height adaptation. ";
}
Else
{
DR[0] = "Test Case" + i + "number";
}
for (int j = 1; j < One; J + +)
{
DR[J] = j;
}
Dt. Rows.Add (DR);
}
if (!isstartprint)
{
Thread th = new Thread (delegate ()
{
Datatabletoexcel (DT, "test. xlsx");
});
Th. Start ();
Isstartprint = true;
}
Else
{
MessageBox.Show ("Print program is enabled, please later ...", "hint", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
Datatabletoexcel (DT, "test. xlsx");
}

public void Datatabletoexcel (System.Data.DataTable dttemp, String strFileName)
{
int rowNum = DtTemp.Rows.Count; Get the number of rows dttemp first
int columnnum = DtTemp.Columns.Count; Number of columns
Excel.Application excelapp = new Excel.Application (); Declaring an instance of an application class

Excelapp.defaultfilepath = ""; Default file path export the path to Excel or set in Parameter strFileName
Excelapp.displayalerts = true;
Excelapp.sheetsinnewworkbook = 1;///Returns or sets the number of worksheets that Microsoft Excel automatically inserts into a new workbook.
Excel.Workbook Worksbook = ExcelApp.Workbooks.Add (); Create a new workbook
Excel.Worksheet Worksheet = (excel.worksheet) worksbook.worksheets[1]; Get sheet in the workbook.
if (WorkSheet = = null)
{
System.Diagnostics.Debug.WriteLine ("Error:worksheet = = null");
Return
}
Worksheet.name = "Test Sheet1"; Name of the worksheet
Worksheet.cells.wraptext=true; Set text wrapping for all columns
WorkSheet.Cells.EntireRow.AutoFit (); Set all columns to automatically adjust row height
#region Drawing Columns
Custom methods, drawing columns to sheet
Rangemark (WorkSheet, "A1", "A2", "Merge vertical column 1");
Rangemark (WorkSheet, "B1", "B2", "Merge vertical column 2");
Rangemark (WorkSheet, "C1", "C2", "Merge vertical column 3");
Rangemark (WorkSheet, "D1", "D2", "Merge vertical column 4");
Rangemark (WorkSheet, "E1", "E2", "Merge vertical column 5");
Rangemark (WorkSheet, "F1", "H1", "merge Rampage 1");
Rangemark (WorkSheet, "F2", "F2", "Merge Rampage 1.1");
Rangemark (WorkSheet, "G2", "G2", "Merge Rampage 1.2");
Rangemark (WorkSheet, "H2", "H2", "Merge Rampage 1.3");
Rangemark (WorkSheet, "I1", "K1", "Merge Rampage 2");
Rangemark (WorkSheet, "I2", "J2", "Merge Rampage 2.1");
Rangemark (WorkSheet, "K2", "K2", "merge Rampage 2.2");
#endregion
Import data from a DataTable into Excel
for (int i = 0; i < rowNum; i++)
{
for (int j = 0; J < Columnnum; J + +)
{
Worksheet.cells[i + 3, j + 1] = Dttemp.rows[i][j]. ToString (); Text

}
}
Save path
String filePath = @ "C:\Users\Admin\Desktop\" + strFileName;
if (file.exists (FilePath))
{
Try
{
File.delete (FilePath);
}
catch (Exception)
{

}
}
Excelapp.visible = true;
------------------------print page related Settings--------------------------------
WorkSheet.PageSetup.PaperSize = excel.xlpapersize.xlpapera4;//Paper size
workSheet.PageSetup.Orientation = excel.xlpageorientation.xllandscape;//Page Landscape
WorkSheet.PageSetup.Zoom = 75; Page setup when printing, percent of scale
WorkSheet.PageSetup.Zoom = false; Page setup when printing, must be set to False, page height, page width is valid
WorkSheet.PageSetup.FitToPagesWide = 1; Set page Zoom page width to 1 page width
WorkSheet.PageSetup.FitToPagesTall = false; Set page height to zoom automatically
WorkSheet.PageSetup.LeftHeader = "Chengdu Jingsheng (Esimtech)";//the flag on the top left of the page
WorkSheet.PageSetup.CenterFooter = "Page &p, total &n page";//page subscript
WorkSheet.PageSetup.PrintGridlines = true; Print Cell network cable
WorkSheet.PageSetup.TopMargin = 1.5/0.035; Top margin is 2cm (converted to in)
WorkSheet.PageSetup.BottomMargin = 1.5/0.035; Bottom margin is 1.5cm
WorkSheet.PageSetup.LeftMargin = 2/0.035; Left margin is 2cm
WorkSheet.PageSetup.RightMargin = 2/0.035; Right margin is 2cm
WorkSheet.PageSetup.CenterHorizontally = true; Text horizontally centered
------------------------Print Page Setup ends--------------------------------
http://blog.csdn.net/wanmingtom/article/details/6125599
Worksbook.printpreview (); Print Preview
Worksbook.printoutex (); Direct printing
Worksbook.close (); To close a working space
Excelapp.quit (); Exit program
Worksheet.saveas (FilePath); Save table
KillProcess (excelapp); Kill the generated process
Isstartprint = false; Print complete
Gc. Collect (); Garbage collection mechanism
}
<summary>
Referencing the Windows handle, getting the program PID
</summary>
<param name= "Hwnd" ></param>
<param name= "PID" ></param>
<returns></returns>
[DllImport ("User32.dll")]
public static extern int GetWindowThreadProcessId (IntPtr Hwnd, out int PID);
<summary>
Kill the generated process
</summary>
<param name= "Appobject" > Process Objects </param>
private static void KillProcess (Excel.Application appobject)
{
int Pid = 0;
IntPtr Hwnd = new IntPtr (Appobject.hwnd);
System.Diagnostics.Process p = null;
Try
{
GetWindowThreadProcessId (Hwnd, out Pid);
p = System.Diagnostics.Process.GetProcessById (Pid);
if (P! = null)
{
P.kill ();
P.dispose ();
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine ("Process shutdown failed! Exception information: "+ ex");
}
}

<summary>
Create TABLE header cells, including merged cells
</summary>
<param name= "WorkSheet" > Worksheets </param>
<param name= "Startcell" > Cell start cell number </param>
<param name= "Endcell" > Cell end Numbering </param>
<param name= "StrText" > Cell name </param>
private static bool Rangemark (Excel.Worksheet Worksheet, String Startcell, String Endcell, String strText)
{
Creates a zone object. The first parameter is the start lattice number, and the second parameter is the terminating lattice number. For example, check the A1--d3 area.
Excel.Range Range = (excel.range) worksheet.get_range (Startcell, Endcell);
if (range = = null)
{
System.Diagnostics.Debug.WriteLine ("Error:range = = null");
return false;
}
Range. Merge (0); Merge method, merged directly into a single cell at 0
Range. Font.Size = 16; Font size
Range. Font.Name = "Blackbody"; Font
Range. WrapText = true; Text Wrap Line
Range. Entirerow.autofit (); Automatically adjust row height
Range. RowHeight = 20;
Range. Entirecolumn.autofit (); Automatically adjust column widths
Range. ColumnWidth = 15;
Range. HorizontalAlignment = Xlvalign.xlvaligncenter; Center horizontally
Range. Value = StrText; After you merge cells, set the text in them
Range. Interior.ColorIndex = 20; Fill Color
Range. Cells.Borders.LineStyle = 1; Set the thickness of a cell border

return true;
}
}
}

C # operations Excel printing