a helper class for generating xls or csv file
a helper class for generating xls or csv file
1 public class XlsOrCsvUtil 2 { 3 public static bool GenerateXls0rCsv(string path, Dictionary<string, List<string>> dicList) 4 { 5 if (dicList == null || dicList.Count <= 1)//title must exist while no data in file 6 { 7 string msg = string.Format("no data need to generate"); 8 throw new Exception(msg); 9 } 10 11 using (ExcelApp app = new ExcelApp(false, false)) 12 { 13 try 14 { 15 Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(app, path); 16 Worksheet wSheet = wBook.Worksheets[1] as Worksheet; 17 FillExcel(wSheet, dicList); 18 app.ExcelAppInstance.AlertBeforeOverwriting = false; 19 wBook.Save(); 20 return true; 21 } 22 catch (Exception ex) 23 { 24 string msg = string.Format("generate XlsOrCsv file error ,msg:{0}", ex.ToString()); 25 throw new Exception(msg); 26 } 27 } 28 } 29 30 public static bool GenerateXls0rCsv(string path, List<List<string>> listList) 31 { 32 if (listList == null || listList.Count <= 1)//title must exist while no data in file 33 { 34 string msg = string.Format("no data need to generate"); 35 throw new Exception(msg); 36 } 37 38 using (ExcelApp app = new ExcelApp(false, false)) 39 { 40 try 41 { 42 Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(app, path); 43 Worksheet wSheet = wBook.Worksheets[1] as Worksheet; 44 FillExcel(wSheet, listList); 45 app.ExcelAppInstance.AlertBeforeOverwriting = false; 46 wBook.Save(); 47 return true; 48 } 49 catch (Exception ex) 50 { 51 string msg = string.Format("generate XlsOrCsv file error ,msg:{0}", ex.ToString()); 52 throw new Exception(msg); 53 } 54 } 55 } 56 57 private static void FillExcel(Worksheet wSheet, List<List<string>> listList) 58 { 59 SetTitle(wSheet, listList[0]); 60 61 for (int i = 1; i < listList.Count; i++) 62 { 63 for (int j = 0; j < listList[i].Count; j++) 64 { 65 ((Range)wSheet.Cells[i + 1, j + 1]).NumberFormatLocal = "@"; 66 wSheet.Cells[i + 1, j + 1] = listList[i][j]; 67 } 68 } 69 } 70 71 private static void FillExcel(Worksheet wSheet, Dictionary<string, List<string>> dic) 72 { 73 int rowCount = dic.Count; 74 SetTitle(wSheet, dic.Values.ToList()[0]); 75 76 for (int i = 1; i < rowCount; i++) 77 { 78 var list = dic.Values.ToList()[i]; 79 for (int j = 0; j < list.Count; j++) 80 { 81 ((Range)wSheet.Cells[i + 1, j + 1]).NumberFormatLocal = "@"; 82 wSheet.Cells[i + 1, j + 1] = list[j]; 83 } 84 } 85 } 86 87 private static void SetTitle(Worksheet wSheet, List<string> list) 88 { 89 for (int i = 0; i < list.Count; i++) 90 { 91 ((Range)wSheet.Columns[ToName(i), System.Type.Missing]).ColumnWidth = 20; 92 wSheet.Cells[1, i + 1] = list[i]; 93 } 94 95 //((Range)wSheet.Columns["A:" + ToName(list.Count - 1), System.Type.Missing]).Font.Name = "Arail";//set style of XlsOrCsv 96 //((Range)wSheet.Rows[1, Type.Missing]).Font.Bold = System.Drawing.FontStyle.Bold; 97 //((Range)wSheet.Rows[1, Type.Missing]).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Black); 98 } 99 100 public static string ToName(int index) 101 { 102 if (index < 0) 103 throw new Exception("invalid parameter"); 104 105 List<string> chars = new List<string>(); 106 107 do 108 { 109 if (chars.Count > 0) index--; 110 chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString()); 111 index = (int)((index - index % 26) / 26); 112 } 113 while (index > 0); 114 115 return String.Join(string.Empty, chars.ToArray()); 116 } 117 118 public static bool GenerateStringCsv(string path, Dictionary<string, List<string>> dicList) 119 { 120 string result = string.Empty; 121 if (dicList == null || dicList.Count <= 1)//title must exist while no data in file 122 { 123 string msg = string.Format("no data need to generate"); 124 throw new Exception(msg); 125 } 126 127 try 128 { 129 StringBuilder sb = new StringBuilder(); 130 foreach (var item in dicList.Values.ToList()) 131 { 132 foreach (var str in item) 133 sb.AppendFormat("{0},", str.Replace(",", "")); 134 135 sb.Length = sb.Length - 1; 136 sb.Append("\r\n"); 137 } 138 139 File.WriteAllText(path, sb.ToString()); 140 return true; 141 } 142 catch (Exception ex) 143 { 144 string msg = string.Format("generate StringCsv file error ,msg:{0}", ex.ToString()); 145 throw new Exception(msg); 146 } 147 } 148 149 public static bool GenerateStringCsv(string path, List<List<string>> listList) 150 { 151 string result = string.Empty; 152 if (listList == null || listList.Count <= 1)//title must exist while no data in file 153 { 154 string msg = string.Format("no data need to generate"); 155 throw new Exception(msg); 156 } 157 158 try 159 { 160 StringBuilder sb = new StringBuilder(); 161 foreach (var item in listList) 162 { 163 foreach (var str in item) 164 sb.AppendFormat("{0},", str.Replace(",", "")); 165 166 sb.Length = sb.Length - 1; 167 sb.Append("\r\n"); 168 } 169 170 File.WriteAllText(path, sb.ToString()); 171 return true; 172 } 173 catch (Exception ex) 174 { 175 string msg = string.Format("generate StringCsv file error ,msg:{0}", ex.ToString()); 176 throw new Exception(msg); 177 } 178 } 179 }
