Excel operations and excel operations

Excel operations and excel operations

Aspose. Cells. dll: You can operate excel without installing office components.

1 using System; 2 using System. web; 3 using Aspose. cells; 4 using System. data; 5 using System. collections. specialized; 6 using System. collections. generic; 7 using System. reflection; 8 using System. IO; 9 10 namespace APP. excelOperation 11 {12 public class AsposeCellsHelper 13 {14 # region export excel Data 15 /// <summary> 16 /// export excel Data 17 /// </summary> 18 // /<param name = "dt"> data table </param> 19 // <param name = "coll"> name of the data column mapped by the title </param> 20 public static void exportExcel (DataTable dt, nameValueCollection coll, string fileName) 21 {22 Workbook workbook = new Workbook (); 23 Worksheet sheet = workbook. worksheets [0]; 24 25 Aspose. cells. style s = new Aspose. cells. style (); 26 s. font. isBold = true; 27 s. font. size = 12; 28 s. number = 49; 29 30 // set the title and format 31 for (int I = 0; I <coll. count; I ++) 32 {33 sheet. cells [0, I]. setStyle (s); 34 sheet. cells [0, I]. value = coll [I]; 35} 36 // fill in content 37 for (int I = 0; I <dt. rows. count; I ++) 38 {39 for (int c = 0; c <coll. count; c ++) 40 {41 sheet. cells [I + 1, c]. value = dt. rows [I] [coll. keys [c]; 42} 43} 44 ResponseFile (workbook, fileName); 45} 46 47 public static void ExportExcel <T> (IEnumerable <T> data, string fileName) 48 {49 Workbook workbook = new Workbook (); 50 Worksheet sheet = (Worksheet) workbook. worksheets [0]; 51 52 PropertyInfo [] ps = typeof (T ). getProperties (); 53 var colIndex = "A"; 54 55 foreach (var p in ps) 56 {57 58 sheet. cells [colIndex + 1]. putValue (p. name); 59 int I = 2; 60 foreach (var d in data) 61 {62 sheet. cells [colIndex + I]. putValue (p. getValue (d, null); 63 I ++; 64} 65 66 colIndex = (char) (colIndex [0] + 1 )). toString (); 67} 68 ResponseFile (workbook, fileName); 69} 70 private static void ResponseFile (Workbook workbook, string fileName) 71 {72 HttpResponse response = HttpContext. current. response; 73 response. clear (); 74 response. buffer = true; 75 response. charset = "UTF-8"; 76 response. appendHeader ("Content-Disposition", "attachment; filename =" + fileName + ". xls "); 77 response. contentEncoding = System. text. encoding. UTF8; 78 response. contentType = "application/ms-excel"; 79 response. binaryWrite (workbook. saveToStream (). toArray (); 80 response. end (); 81} 82 83 # endregion export excel Data end 84 85 # region read excel Data 86 87 // <summary> 88 // read excel file flow to able 89 // </summary> 90 // <param name = "fileStream"> </param> 91 // <returns> </returns> 92 public static DataTable ReadExcel (Stream fileStream) 93 {94 Workbook book = new Workbook (fileStream); 95 Worksheet sheet = book. worksheets [0]; 96 Cells = sheet. cells; 97 return cells. exportDataTable (0, 0, cells. maxDataRow + 1, cells. maxDataColumn + 1, true); 98} 99 100 // <summary> 101 // read the excel file flow to datatable, the format is string102 // 103 is recommended /// </summary> 104 // <param name = "fileStream"> </param> 105 // <returns> </returns> 106 public static DataTable ReadExcelAsString (Stream fileStream) 107 {108 Workbook book = new Workbook (fileStream); 109 Worksheet sheet = book. worksheets [0]; 110 Cells = sheet. cells; 111 return cells. exportDataTableAsString (0, 0, cells. maxDataRow + 1, cells. maxDataColumn + 1, true); 112} 113 114 // <summary> 115 // read the excel file flow to DataSet, the format is string116 // 117 is recommended /// </summary> 118 // <param name = "fileStream"> </param> 119 // <returns> </returns> 120 public static DataSet ReadExcelAsStringToDataSet (Stream fileStream) 121 {122 Workbook book = new Workbook (fileStream); 123 DataSet ds = new DataSet (); 124 for (int I = 0; I <book. worksheets. count; I ++) 125 {126 Worksheet sheet = book. worksheets [I]; 127 Cells = sheet. cells; 128 if (cells. rows. count> 0) 129 {130 var dt = cells. exportDataTableAsString (0, 0, cells. maxDataRow + 1, cells. maxDataColumn + 1, true); 131 dt. tableName = sheet. name; 132 ds. tables. add (dt); 133} 134} 135 return ds; 136} 137 138 // <summary> 139 // read the excel file to ableable140 // </summary> 141 // <param name = "fileStream"> </ param> 142 // <returns> </returns> 143 public static DataTable ReadExcel (String strFileName) 144 {145 Workbook book = new Workbook (strFileName); 146 Worksheet sheet = book. worksheets [0]; 147 Cells = sheet. cells; 148 return cells. exportDataTable (0, 0, cells. maxDataRow + 1, cells. maxDataColumn + 1, true); 149} 150 151 // <summary> 152 // read the excel file to the datatable, the format is string153 // 154 is recommended /// </summary> 155 // <param name = "fileStream"> </param> 156 // <returns> </returns> 157 public static DataTable ReadExcelAsString (String strFileName) 158 {159 Workbook book = new Workbook (strFileName); 160 Worksheet sheet = book. worksheets [0]; 161 Cells = sheet. cells; 162 return cells. exportDataTableAsString (0, 0, cells. maxDataRow + 1, cells. maxDataColumn + 1, true); 163} 164 # endregion165 166} 167}View Code