XLS>DATATABLE>PDF

 1 public static DataSet ExcelToDS(string Pathm, string TableName)
 2         {
 3             TableName = ExcelSheetName(Pathm)[0].ToString();
 4             DataSet ds = new DataSet();
 5             //try
 6             //{
 7             //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "DataSource=" + Pathm + ";" + "ExtendedProperties='Excel8.0;'";
 8             string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Pathm + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
 9             OleDbConnection conn = new OleDbConnection(strConn);
10             conn.Open();
11             string strExcel = "";
12             OleDbDataAdapter myCommand = null;
13             strExcel = string.Format("select * from[{0}]", TableName);
14             myCommand = new OleDbDataAdapter(strExcel, strConn);
15 
16             myCommand.Fill(ds, TableName);
17             //}
18             //catch (Exception ex)
19             //{
20 
21             //}
22             return ds;
23         }
24 
25         //Excel中的所有sheetname。
26         public static System.Collections.ArrayList ExcelSheetName(string filepath)
27         {
28             System.Collections.ArrayList al = new System.Collections.ArrayList();
29             //string strConn;
30             string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
31             //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
32             OleDbConnection conn = new OleDbConnection(strConn);
33             conn.Open();
34             DataTable sheetNames = conn.GetOleDbSchemaTable
35             (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
36             conn.Close();
37             foreach (DataRow dr in sheetNames.Rows)
38             {
39                 al.Add(dr[2]);
40             }
41             return al;
42         }
43 
44         public void GetPdf(DataTable datatable)
45         {
46             Document document = new Document();
47             PdfWriter.GetInstance(document, new FileStream(Server.MapPath("/UploadImages/Chap0101.pdf"), FileMode.Create));
48                         document.Open();
49             BaseFont bfChinese = BaseFont.CreateFont("C:\\WINDOWS\\Fonts\\simsun.ttc,1", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
50             Font fontChinese = new Font(bfChinese, 12, Font.NORMAL,new BaseColor(0, 0, 0));
51 
52             //document.Add(new Paragraph("PDF文檔打印測試\n\n", fontChinese));
53 
54             //iTextSharp.text.Image jpeg = iTextSharp.text.Image.GetInstance(Server.MapPath("pic015.jpg"));
55             //document.Add(jpeg);
56             PdfPTable table = new PdfPTable(datatable.Columns.Count);
57 
58             for (int i = 0; i < datatable.Rows.Count; i++)
59             {
60                 for (int j = 0; j < datatable.Columns.Count; j++)
61                 {
62                     table.AddCell(new Phrase(datatable.Rows[i][j].ToString(), fontChinese));
63                 }
64             }
65             document.Add(table);
66 
67             document.Close();
68         }