c#生成Excel,可建立sheet,设定sheet名称,调整列宽度.doc

Excel ExcelRW.cs C# 1. using System; 2. using System.Data; 3. using System.Configuration; 4. using System.Web; 5. using System.Web.Security; 6. using System.Web.UI; 7. using System.Web.UI.WebControls; 8. using System.Web.UI.WebControls.WebParts; 9. using System.Web.UI.HtmlControls; 10. using Microsoft.Office.Interop.Excel; 11. using System.Collections; 12. using System.Diagnostics; 13. 14. / 15. / Summary description for ExcelRW 16. / 17. public static class ExcelRW 18. 19. 20. 21. public static void ExcelSave(Hashtable ht) 22. 23. 24. Application excel = new Application();/Excel 25. 26. Workbooks oBooks; 27. Workbook oBook; 28. Sheets oSheets; 29. Worksheet oSheet; 30. Range oCells; 31. string sFile = htexcelName as string; 32. /excel.Save(ddd.xls); 33. ArrayList alSheet = htsheetNameList as ArrayList; 34. Workbook wb = excel.Workbooks.Add(true);/Excel 35. excel.Visible = false;/Excel 36. oBooks = excel.Workbooks; 37. oBook = oBooks.get_Item(1); 38. oSheets = oBook.Worksheets; 39. oSheet = (Worksheet)oSheets.get_Item(oSheets.Count); 40. foreach (Hashtable htOneSheel in alSheet) 41. 42. 43. 44. oSheet = (Worksheet)oSheets.get_Item(oSheets.Count); 45. 46. ArrayList ColNameList = htOneSheelColName as ArrayList; 47. /sheet 48. oSheet.Name = htOneSheelsheetName as string; 49. System.Data.DataTable dtable = htOneSheeldataRows as System.Data.DataTable; 50. Range column = (Range)oSheet.Cells1, 1).EntireColumn; 51. column.ColumnWidth = 10; 52. int iRow = 1; 53. foreach (DataRow dr in dtable.Rows) 54. 55. int icel = 1; 56. for (int iCol = 0; iCol ColNameList.Count; iCol+) 57. 58. oSheet.CellsiRow, icel+ = Convert.ToString(drColNameListiCol.ToString(); 59. 60. 61. iRow+; 62. 63. oSheets.Add(Type.Missing, oSheetsoSheets.Count, 1, Type.Missing); 64. /sheetsheet 65. /(Worksheet)oSheets.get_Item(1).Activate(); 66. oSheet = (Worksheet)oSheets.get_Item(1); 67. 68. 69. string strFilePathAndname = HttpContext.Current.Server.MapPath(.) + /Excel/ + sFile; 70. 71. if (System.IO.File.Exists(strFilePathAndname) 72. 73. System.IO.File.Delete(strFilePathAndname); 74. 75. /excel.Workbooks.get_Item(1).Worksheets.Select(excel.Workbooks.get_Item(1).Worksheets1); 76. oSheet.SaveAs(strFilePathAndname, Microsoft.Office.Interop.Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); 77. oBook.Close(false, Type.Missing, Type.Missing); 78. /ExcelCOM 79. excel.Quit(); 80. 81. GC.Collect(); 82. /excel.Save(ddd.xls); 83. KillProcess(Excel); 84. 85. 86. 87. public static void Out2Excel(string sTableName, string url) 88. 89. Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); 90. Workbooks oBooks; 91. Workbook oBook; 92. Sheets oSheets; 93. Worksheet oSheet; 94. Range oCells; 95. string sFile = , sTemplate = ; 96. / 97. System.Data.DataTable dt = null;/ TableOut(sTableName).Tables0; 98. 99. sFile = url + myExcel.xls; 100. sTemplate = url + MyTemplate.xls; 101. / 102. oExcel.Visible = false; 103. oExcel.DisplayAlerts = false; 104. / 105. oBooks = oExcel.Workbooks; 106. oBooks.Open(sTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 107. oBook = oBooks.get_Item(1); 108. oSheets = oBook.Worksheets; 109. 110. oSheet = (Worksheet)oSheets.get_Item(1); 111. /sheet 112. oSheet.Name = Sheet1; 113. /(Worksheet)oSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); 114. oCells = oSheet.Cells; 115. /dumpdataExcel 116. /DumpData(dt, oCells); 117. / 118. oSheet.SaveAs(sFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); 119. oBook.Close(false, Type.Missing, Type.Missing); 120. /ExcelCOM 121. oExcel.Quit(); 122. 123. GC.Collect(); 124. KillProcess(Excel); 125. 126. 127. private static void KillProcess(string processName) 128. 129. System.Diagnostics.Process myproc = new System.Diagnostics.Process(); 130. / 131. try 132. 133. foreach (Process thisproc in Process.GetProcessesByName(processName) 134. 135. if (!thisproc.CloseMainWindow() 136. 137. thisproc.Kill(); 138. 139. 140. 141. catch (Exception Exc) 142. 143. throw new Exception(, Exc); 144. 145. 146. 147. using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Microsoft.Office.Interop.Excel;using System.Collections;using System.Diagnostics;/ / Summary description for ExcelRW/ public static class ExcelRW public static void ExcelSave(Hashtable ht) Application excel = new Application();/Excel Workbooks oBooks; Workbook oBook; Sheets oSheets; Worksheet oSheet; Range oCells; string sFile = htexcelName as string; /excel.Save(ddd.xls); ArrayList alSheet = htsheetNameList as ArrayList; Workbook wb = excel.Workbooks.Add(true);/Excel excel.Visible = false;/Excel oBooks = excel.Workbooks; oBook = oBooks.get_Item(1); oSheets = oBook.Worksheets; oSheet = (Worksheet)oSheets.get_Item(oSheets.Count); foreach (Hashtable htOneSheel in alSheet) oSheet = (Worksheet)oSheets.get_Item(oSheets.Count); ArrayList ColNameList = htOneSheelColName as ArrayList; /sheet oSheet.Name = htOneSheelsheetName as string; System.Data.DataTable dtable = htOneSheeldataRows as System.Data.DataTable; Range column = (Range)oSheet.Cells1, 1).EntireColumn; column.ColumnWidth = 10; int iRow = 1; foreach (DataRow dr in dtable.Rows) int icel = 1; for (int iCol = 0; iCol ColNameList.Count; iCol+) oSheet.CellsiRow, icel+ = Convert.ToString(drColNameListiCol.ToString(); iRow+; oSheets.Add(Type.Missing, oSheetsoSheets.Count, 1, Type.Missing); /sheetsheet /(Worksheet)oSheets.get_Item(1).Activate(); oSheet = (Worksheet)oSheets.get_Item(1); string strFilePathAndname = HttpContext.Current.Server.MapPath(.) + /Excel/ + sFile; if (System.IO.File.Exists(strFilePathAndname) System.IO.File.Delete(strFilePathAndname); /excel.Workbooks.get_Item(1).Worksheets.Select(excel.Workbooks.get_Item(1).Worksheets1); oSheet.SaveAs(strFilePathAndname, Microsoft.Office.Interop.Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); oBook.Close(false, Type.Missing, Type.Missing); /ExcelCOM excel.Quit(); GC.Collect(); /excel.Save(ddd.xls); KillProcess(Excel); public static void Out2Excel(string sTableName, string url) Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); Workbooks oBooks; Workbook oBook; Sheets oSheets; Worksheet oSheet; Range oCells; string sFile = , sTemplate = ; / System.Data.DataTable dt = null;/ TableOut(sTableName).Tables0; sFile = url + myExcel.xls; sTemplate = url + MyTemplate.xls; / oExcel.Visible = false; oExcel.DisplayAlerts = false; / oBooks = oExcel.Workbooks; oBooks.Open(sTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); oBook = oBooks.get_Item(1); oSheets = oBook.Worksheets; oSheet = (Worksheet)oSheets.get_Item(1); /sheet oSheet.Name = Sheet1; /(Worksheet)oSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); oCells = oSheet.Cells; /dumpdataExcel /DumpData(dt, oCells); / oSheet.SaveAs(sFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); oBook.Close(false, Type.Missing, Type.Missing); /ExcelCOM oExcel.Quit(); GC.Collect(); KillProcess(Excel); private static void KillProcess(string processName) System.Diagnostics.Process myproc = new System.Diagnostics.Process(); / try foreach (Process thisproc in Process.GetProcessesByName(processName) if (!thisproc.CloseMainWindow() thisproc.Kill(); catch (Exception Exc) throw new Exception(, Exc); testExcelDown.aspxC# 1. 2. 3. 4. 5. 6. 7. Untitled Page 8. 9. 10. 11. 12. 13. 14. 15. Untitled Page testExcelDown.aspx.cs C# 1. using System; 2. using System.Data; 3. using System.Configuration; 4. using System.Collections; 5. using System.Web; 6. using System.Web.Security; 7. using System.Web.UI; 8. using System.Web.UI.WebControls; 9. using System.Web.UI.WebControls.WebParts; 10. using System.Web.UI.HtmlControls; 11. 12. public partial class testExcelDown : System.Web.UI.Page 13. 14. protected void Page_Load(object sender, EventArgs e) 15. 16. 17. 18. protected void bnt_test_Click(object sender, EventArgs e) 19. 20. DataSearch(); 21. 22. 23. 24. / 25. / excel 26. / 27. private void DataSearch() 28. 29. ArrayList arColName = new ArrayList(); 30. ArrayList alData = new ArrayList(); 31. 32. arColName.Add(title); 33. arColName.Add(url); 34. DataTable dtSheet1 = new DataTable(); 35. dtSheet1.Columns.Add(new DataColumn(title); 36. dtSheet1.Columns.Add(new DataColumn(url); 37. /sheet1 38. 39. 40. 41. DataRow dr = dtSheet1.NewRow(); 42. drtitle = ; 43. drurl = ; 44. dtSheet1.Rows.Add(dr); 45. dr = dtSheet1.NewRow(); 46. drtitle = 400; 47. drurl = ; 48. dtSheet1.Rows.Add(dr); 49. dr = dtSheet1.NewRow(); 50. drtitle = goole; 51. drurl = ; 52. dtSheet1.Rows.Add(dr); 53. dr = dtSheet1.NewRow(); 54. drtitle = sina; 55. drurl = ; 56. dtSheet1.Rows.Add(dr); 57. dr = dtSheet1.NewRow(); 58. drtitle = tel4006; 59. drurl = ; 60. dtSheet1.Rows.Add(dr); 61. dr = dtSheet1.NewRow(); 62. drtitle = soso; 63. drurl = ; 64. dtSheet1.Rows.Add(dr); 65. dr = dtSheet1.NewRow(); 66. drtitle = gougou; 67. drurl = ; 68. dtSheet1.Rows.Add(dr); 69. 70. Hashtable htOneType = new Hashtable(); 71. 72. htOneTypedataRows = dtSheet1; 73. htOneTypeColName = arColName; 74. htOneTypesheetName = 1; 75. alData.Add(htOneType); 76. /sheet2 77. 78. dr = dtSheet1.NewRow(); 79. drtitle = MP3; 80. drurl = http:/MP3.