将CSV文件另存为excel
①将csv读取到datatable
②将datatable写到空白exel(使用NPOI)
/// <summary> /// 获取CSV文件 /// </summary> public void GetCSVFile() { string path = @"E:\isec"; DirectoryInfo root = new DirectoryInfo(path); FileInfo[] files = root.GetFiles(); foreach (FileInfo item in files) { if (item.FullName.ToLower().Contains(".csv")) { CSVFileHelper dddd = new CSVFileHelper(); string PATH = item.FullName; DataTable dt = dddd.OpenCSV(PATH); //将datatable转成excel string Path = item.FullName.ToLower().Replace(".csv", string.Format("{0}{1}", DateTime.Now.ToString("yyyyMMddHHmmssfff"), ".xlsx")); ExcelHelperExcel excelHelper = new ExcelHelperExcel(); excelHelper.SaveToExcel(Path, dt, ref _IsSuc, ref _Msg); } } }
//自定义一个类 /// <summary> /// CSV文件操作类 /// </summary> public class CSVFileHelper { public DataTable OpenCSV(string filePath)//从csv读取数据返回table { System.Text.Encoding encoding = GetType(filePath); //Encoding.ASCII;// DataTable dt = new DataTable(); System.IO.FileStream fs = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read); System.IO.StreamReader sr = new System.IO.StreamReader(fs, encoding); //记录每次读取的一行记录 string strLine = ""; //记录每行记录中的各字段内容 string[] aryLine = null; string[] tableHead = null; //标示列数 int columnCount = 0; //标示是否是读取的第一行 bool IsFirst = true; //逐行读取CSV中的数据 while ((strLine = sr.ReadLine()) != null) { if (IsFirst == true) { tableHead = strLine.Split(','); IsFirst = false; columnCount = tableHead.Length; //创建列 for (int i = 0; i < columnCount; i++) { DataColumn dc = new DataColumn(tableHead[i]); dt.Columns.Add(dc); } } else { aryLine = strLine.Split(','); DataRow dr = dt.NewRow(); for (int j = 0; j < columnCount; j++) { try { if (aryLine.Length > j) dr[j] = aryLine[j]; } catch (Exception) { continue; } } dt.Rows.Add(dr); } } if (aryLine != null && aryLine.Length > 0) { dt.DefaultView.Sort = tableHead[0] + " " + "asc"; } sr.Close(); fs.Close(); return dt; } public DataTable OpenCSV2(string filePath)//从csv读取数据返回table { System.Text.Encoding encoding = GetType(filePath); //Encoding.ASCII;// DataTable dt = new DataTable(); System.IO.FileStream fs = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read); System.IO.StreamReader sr = new System.IO.StreamReader(fs, encoding); //记录每次读取的一行记录 string strLine = ""; //记录每行记录中的各字段内容 string[] aryLine = null; string[] tableHead = null; //标示列数 int columnCount = 0; //标示是否是读取的第一行 bool IsFirst = true; //逐行读取CSV中的数据 while ((strLine = sr.ReadLine()) != null) { if (IsFirst == true) { tableHead = strLine.Split('\t'); IsFirst = false; columnCount = tableHead.Length; for (int i = 0; i < 20; i++) { DataColumn dc = new DataColumn(i.ToString()); dt.Columns.Add(dc); } //创建列 DataRow dr = dt.NewRow(); for (int j = 0; j < tableHead.Count(); j++) { dr[j] = tableHead[j]; } dt.Rows.Add(dr); } else { try { aryLine = strLine.Split('\t'); DataRow dr = dt.NewRow(); for (int j = 0; j < aryLine.Count(); j++) { dr[j] = aryLine[j]; } dt.Rows.Add(dr); } catch (Exception) { throw; } } } sr.Close(); fs.Close(); return dt; } /// 给定文件的路径,读取文件的二进制数据,判断文件的编码类型 /// <param name="FILE_NAME">文件路径</param> /// <returns>文件的编码类型</returns> public System.Text.Encoding GetType(string FILE_NAME) { System.IO.FileStream fs = new System.IO.FileStream(FILE_NAME, System.IO.FileMode.Open, System.IO.FileAccess.Read); System.Text.Encoding r = GetType(fs); fs.Close(); return r; } /// 通过给定的文件流,判断文件的编码类型 /// <param name="fs">文件流</param> /// <returns>文件的编码类型</returns> public System.Text.Encoding GetType(System.IO.FileStream fs) { byte[] Unicode = new byte[] { 0xFF, 0xFE, 0x41 }; byte[] UnicodeBIG = new byte[] { 0xFE, 0xFF, 0x00 }; byte[] UTF8 = new byte[] { 0xEF, 0xBB, 0xBF }; //带BOM System.Text.Encoding reVal = System.Text.Encoding.Default; System.IO.BinaryReader r = new System.IO.BinaryReader(fs, System.Text.Encoding.Default); int i; int.TryParse(fs.Length.ToString(), out i); byte[] ss = r.ReadBytes(i); if (IsUTF8Bytes(ss) || (ss[0] == 0xEF && ss[1] == 0xBB && ss[2] == 0xBF)) { reVal = System.Text.Encoding.UTF8; } else if (ss[0] == 0xFE && ss[1] == 0xFF && ss[2] == 0x00) { reVal = System.Text.Encoding.BigEndianUnicode; } else if (ss[0] == 0xFF && ss[1] == 0xFE && ss[2] == 0x41) { reVal = System.Text.Encoding.Unicode; } r.Close(); return reVal; } /// 判断是否是不带 BOM 的 UTF8 格式 /// <param name="data"></param> /// <returns></returns> private bool IsUTF8Bytes(byte[] data) { int charByteCounter = 1; //计算当前正分析的字符应还有的字节数 byte curByte; //当前分析的字节. for (int i = 0; i < data.Length; i++) { curByte = data[i]; if (charByteCounter == 1) { if (curByte >= 0x80) { //判断当前 while (((curByte <<= 1) & 0x80) != 0) { charByteCounter++; } //标记位首位若为非0 则至少以2个1开始 如:110XXXXX...........1111110X if (charByteCounter == 1 || charByteCounter > 6) { return false; } } } else { //若是UTF-8 此时第一位必须为1 if ((curByte & 0xC0) != 0x80) { return false; } charByteCounter--; } } if (charByteCounter > 1) { throw new Exception("非预期的byte格式"); } return true; } }
/// <summary> /// 将table写入空白exel /// </summary> /// <param name="Path"></param> /// <param name="dt"></param> /// <param name="_IsSuc"></param> /// <param name="_Msg"></param> public void SaveToExcel(string ExportPath, DataTable dt, ref bool _IsSuc, ref string _Msg) { _IsSuc = false; _Msg = "导出失败"; IWorkbook workbook = null; FileInfo item = new System.IO.FileInfo(System.Environment.CurrentDirectory + @"\APP_Data\SaveModel.xlsx"); ExcelHelperExcel excelHelper = new ExcelHelperExcel(); var excelFileStream = FileToStream(item.FullName); int typeExcel = Path.GetExtension(item.Name) == ".xls" ? 0 : 1; using (excelFileStream) { try { if (typeExcel == 0) { workbook = new HSSFWorkbook(excelFileStream); } else { workbook = new XSSFWorkbook(excelFileStream); } ISheet sheet = workbook.GetSheetAt(0); //取第一个表 int HeaderRow = 1; int FjColumIndex = 0; IRow CurrentRow = sheet.CreateRow(HeaderRow); #region 将数据写入流 ///写入header for (int i = 0; i < dt.Columns.Count; i++) { CurrentRow.CreateCell(i); CurrentRow.GetCell(i).SetCellValue(dt.Columns[i].Caption); if (dt.Columns[i].Caption.Contains("附件")) { FjColumIndex = i; sheet.SetColumnWidth(FjColumIndex, 60 * 256); } } HeaderRow = HeaderRow + 1; bool IsNewRow = true; int StartRow = 0; ///写入表格内容 for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; #region 新增行 //判断是否是新行 if (dr.ItemArray[0].ToString().Contains("附件") && string.IsNullOrEmpty(dr.ItemArray[1].ToString())) { IsNewRow = false; } else { IsNewRow = true; } if (IsNewRow) { if (StartRow != 0) { //单元格合并 for (int c = 0; c < dt.Columns.Count; c++) { sheet.AddMergedRegion(new CellRangeAddress(StartRow, HeaderRow + i-1, c, c)); } } CurrentRow = sheet.CreateRow(HeaderRow + i); StartRow = HeaderRow + i; } #endregion #region 添加数据 if (IsNewRow) { for (int a = 0; a < dr.ItemArray.Count(); a++) { if (a == FjColumIndex) { ICell notesTitle = sheet.GetRow(StartRow).CreateCell(a); ICellStyle notesStyle = workbook.CreateCellStyle(); notesStyle.WrapText = true;//设置换行这个要先设置 notesTitle.CellStyle = notesStyle;//设置换行 notesTitle.SetCellValue(dr.ItemArray[a].ToString()); //sheet.GetRow(StartRow).CreateCell(a).SetCellValue(dr.ItemArray[a].ToString()); } else { sheet.GetRow(StartRow).CreateCell(a).SetCellValue(dr.ItemArray[a].ToString()); } } } else { string fj = sheet.GetRow(StartRow).GetCell(FjColumIndex).ToString() + @"\n"; sheet.GetRow(StartRow).GetCell(FjColumIndex).SetCellValue((fj + dr.ItemArray[0].ToString()).TrimEnd('\n')); } #endregion } #endregion //模板数据 MemoryStream ms = new MemoryStream(); //写入 workbook.Write(ms); using (FileStream fs = new FileStream(ExportPath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); ms.Close(); ms.Dispose(); } _IsSuc = true; } catch (Exception ex) { throw ex; } finally { if (workbook != null) workbook.Close(); } } }