封装一个ExcelHelper,方便将Excel直接转成Datatable对象
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
namespace Excel.common
{
public class ExcelTranHelper
{
/// <summary>
/// Excel转换成DataTable
/// </summary>
/// <param name="excelFilePath">excel文件路径</param>
/// <param name="sheetNum">sheet序号</param>
/// <param name="headerRowNum">标题列序号</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string excelFilePath, int sheetNum = 0, int headerRowNum = 0, bool AllowColumRepetition = false)
{
IWorkbook workbook;
DataTable dt;
string extension = Path.GetExtension(excelFilePath).ToLower();
try {
using (FileStream fileStream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
if (excelFilePath.Contains(".xlsx"))
{ workbook = new XSSFWorkbook(fileStream); }
else
{ workbook = new HSSFWorkbook(fileStream); }
ISheet sheet = workbook.GetSheetAt(sheetNum);
dt = new DataTable(sheet.SheetName);
IRow headerRow = sheet.GetRow(headerRowNum);
string fieldName = "";
for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum; i++)
{
if (headerRow.GetCell(i) != null)
{
fieldName = headerRow.GetCell(i).ToString().Trim();
DataColumn column = new DataColumn(fieldName);
if (AllowColumRepetition && dt.Columns.Contains(column.ColumnName))
{
column.ColumnName = column.ColumnName + i;
}
dt.Columns.Add(column);
}
else
{
break;
}
}
DataRow dr;
IRow row;
ICell cell;
//short format;
for (int i = headerRowNum + 1; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (row != null)
{
dr = dt.NewRow();
for (int j = headerRow.FirstCellNum; j < headerRow.LastCellNum; j++)
{
cell = row.GetCell(j);
Debug.WriteLine(i.ToString(), j.ToString());
if (cell != null)
{
//单元格的类型为公式，返回公式的值
if (cell.CellType == CellType.Formula)
{
try
{
//是日期型
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
try
{
dr[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
catch (Exception ex)
{
throw ex;
}
}
//不是日期型
else
{
try
{
dr[j] = cell.NumericCellValue.ToString();
}
catch (Exception ex)
{
throw ex;
}
}
}
catch (Exception ex)
{
}
}
//单元的类型不为公式
else
{
try
{
dr[j] = cell.ToString().Trim() == "" ? null : cell.ToString().Trim();
}
catch (Exception ex)
{
throw ex;
}
}
}
else
{
dr[j] = null;
}
}
dt.Rows.Add(dr);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return RemoveEmpty(dt);
}
/// <summary>
/// Excel转换成DataTable
/// </summary>
/// <param name="excelFilePath">excel文件路径</param>
/// <param name="sheetNum">sheet序号</param>
/// <param name="headerRowNum">标题列序号</param>
/// <returns></returns>
public static DataTable ExcelStreamToDataTable(Stream Stream, string FileName, int sheetNum = 0, int headerRowNum = 0, bool AllowColumRepetition = false)
{
IWorkbook workbook;
DataTable dt;
try
{
using (Stream stream = Stream)
{
if (FileName.Contains(".xlsx"))
{ workbook = new XSSFWorkbook(stream); }
else
{ workbook = new HSSFWorkbook(stream); }
ISheet sheet = workbook.GetSheetAt(sheetNum);
dt = new DataTable(sheet.SheetName);
IRow headerRow = sheet.GetRow(headerRowNum);
string fieldName = "";
for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum; i++)
{
if (headerRow.GetCell(i) != null)
{
fieldName = headerRow.GetCell(i).ToString().Trim();
DataColumn column = new DataColumn(fieldName);
if (AllowColumRepetition && dt.Columns.Contains(column.ColumnName))
{
column.ColumnName = column.ColumnName + i;
}
dt.Columns.Add(column);
}
else
{
break;
}
}
DataRow dr;
IRow row;
ICell cell;
//short format;
for (int i = headerRowNum + 1; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (row != null)
{
dr = dt.NewRow();
for (int j = headerRow.FirstCellNum; j < headerRow.LastCellNum; j++)
{
cell = row.GetCell(j);
if (cell != null)
{
//单元格的类型为公式，返回公式的值
if (cell.CellType == CellType.Formula)
{
//是日期型
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dr[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
//不是日期型
else
{
dr[j] = cell.NumericCellValue.ToString();
}
}
//单元的类型不为公式
else
{
dr[j] = cell.ToString().Trim() == "" ? null : cell.ToString().Trim();
}
}
else
{
dr[j] = null;
}
}
dt.Rows.Add(dr);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return RemoveEmpty(dt);
}
/// <summary>
/// Excel转换成DataTable
/// </summary>
/// <param name="excelFilePath">excel文件路径</param>
/// <param name="sheetName">sheet名称</param>
/// <param name="headerRowNum">标题列序号</param>
/// <param name="AllowColumRepetition">允许列重复</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string excelFilePath, string sheetName, int headerRowNum = 0, bool AllowColumRepetition = false)
{
IWorkbook workbook;
DataTable dt;
string extension = Path.GetExtension(excelFilePath).ToLower();
try
{
using (FileStream fileStream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fileStream);
ISheet sheet;
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
dt = new DataTable(sheet.SheetName);
IRow headerRow = sheet.GetRow(headerRowNum);
string fieldName = "";
//ArrayList fieldArray = new ArrayList();
/*
增加标题列
*/
for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum; i++)
{
if (headerRow.GetCell(i) != null)
{
fieldName = headerRow.GetCell(i).ToString().Trim();
DataColumn column = new DataColumn(fieldName);
if (AllowColumRepetition && dt.Columns.Contains(column.ColumnName))
{
column.ColumnName = column.ColumnName + i;
}
dt.Columns.Add(column);
}
else
{
break;
}
}
DataRow dr;
IRow row;
ICell cell;
//short format;
for (int i = headerRowNum + 1; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (row != null)
{
dr = dt.NewRow();
for (int j = headerRow.FirstCellNum; j < headerRow.LastCellNum; j++)
{
cell = row.GetCell(j);
if (cell != null)
{
//单元格的类型为公式，返回公式的值
if (cell.CellType == CellType.Formula)
{
//是日期型
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dr[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
//不是日期型
else
{
dr[j] = cell.NumericCellValue.ToString();
}
}
//单元的类型不为公式
else
{
dr[j] = cell.ToString().Trim() == "" ? null : cell.ToString().Trim();
}
}
else
{
dr[j] = null;
}
}
dt.Rows.Add(dr);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return RemoveEmpty(dt);
}
/// <summary>
/// 获取Excel里Sheet总数
/// </summary>
/// <param name="excelFilePath"></param>
/// <returns></returns>
public static int GetExcelSheetTotal(string excelFilePath)
{
IWorkbook workbook;
DataTable dt;
string extension = Path.GetExtension(excelFilePath).ToLower();
try
{
using (FileStream fileStream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
if (extension == ".xlsx")
{ workbook = new XSSFWorkbook(fileStream); }
else
{ workbook = new HSSFWorkbook(fileStream); }
return workbook.NumberOfSheets;
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将datatable导入到exel
/// </summary>
/// <param name="datatemp"></param>
/// <param name="fileName"></param>
///<param name="removeEmpty">是否去除所有值都为空的列</param>
///<param name="cusColumStyleDic">dic(ColumeName,cellStyle1.DataFormat);</param>
/// <returns></returns>
public static int DataTableToExcel(DataTable datatemp, string fileName, bool removeEmpty = true, bool isColumnWritten = true, string sheetName = "Sheet1", Dictionary<string,string> cusColumStyleDic = null)
{
DataTable data = removeEmpty ? RemoveEmpty(datatemp) : datatemp;
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
IWorkbook workbook = null;
//文件是否存在
if (File.Exists(fileName))
{
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(file);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(file);
}
}
}
else
{
using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
}
}
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CenterSelection;
try
{
if (workbook != null)
{
int sheetIndex = workbook.GetSheetIndex(sheetName);
if (sheetIndex >= 0) workbook.RemoveSheetAt(sheetIndex);
sheet = workbook.CreateSheet(sheetName);
}
else
{
return -1;
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
IFont font = workbook.CreateFont();
font.FontName = "宋体";
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
cellStyle.SetFont(font);
//单元格样式
row.GetCell(j).CellStyle = cellStyle;
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
var result = 0;
if (cusColumStyleDic?.ContainsKey(data.Columns[j].ColumnName) ?? false)
{
ICellStyle cStyle1 = workbook.CreateCellStyle();
cStyle1.DataFormat = workbook.CreateDataFormat().GetFormat(cusColumStyleDic[data.Columns[j].ColumnName]);//单元格格式 数值
var cell = row.CreateCell(j, CellType.Numeric);
cell.CellStyle = cStyle1;
cell.SetCellValue(Double.Parse(data.Rows[i][j].ToString()));
}
else
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
}
++count;
}
for (i = 0; i < data.Columns.Count; ++i)
{
//设置自动列宽
sheet.AutoSizeColumn(i);
}
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
{
workbook.Write(fs); //写入到excel
}
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
/// <summary>
/// 对Excel里Sheet进行指定顺序排序
/// </summary>
/// <param name="excelFilePath"></param>
/// <param name="orderDic">顺序集合(sheetName,index)</param>
/// <returns></returns>
public static void ExcelSetSheetOrder(string fileName, Dictionary<string, int> orderDic)
{
IWorkbook workbook = null;
DataTable dt;
try
{
//文件是否存在
if (File.Exists(fileName))
{
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(file);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(file);
}
}
}
else
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
}
}
foreach (var item in orderDic)
{
workbook.SetSheetOrder(item.Key, item.Value);
}
using (FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
{
workbook.Write(fileStream); //写入到excel
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 将datatable集合导入到exel
/// </summary>
/// <param name="datatemp"></param>
/// <param name="fileName"></param>
///<param name="removeEmpty">是否去除所有值都为空的列</param>
/// <returns></returns>
public static int DataTableListToExcel(List<DataTable> datatemps, string fileName, List<string> sheetNames, bool removeEmpty = true, bool isColumnWritten = true)
{
int i = 0;
int j = 0;
int count = 0;
IWorkbook workbook = null;
using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CenterSelection;
try
{
//要创建的工作表的张数和DataTable列表数目一致才可以导出
if (sheetNames.Count > 0 && sheetNames.Count == datatemps.Count)
{
int index = 0;
sheetNames.ForEach(s =>
{
ISheet sheet = null;
if (workbook != null)
{
sheet = workbook.CreateSheet(s);
}
DataTable data = removeEmpty ? RemoveEmpty(datatemps[index]) : datatemps[index];
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = (short)15F;
font.Boldweight = (short)FontBoldWeight.Bold;
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
cellStyle.SetFont(font);
//单元格样式
row.GetCell(j).CellStyle = cellStyle;
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
for (i = 0; i < data.Columns.Count; ++i)
{
//设置自动列宽
sheet.AutoSizeColumn(i);
}
index++;
});
}
workbook.Write(fs); //写入到excel并保存
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
}
/// <summary>
/// 按条件修改指定Sheet的值
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="referenceColumName">参照列名称</param>
/// <param name="targetColumnName">要修改值的目标列名称</param>
/// <param name="modifyDic">(参照列值,目标列结果值)集合</param>
public static void UpdateExcelData(string fileName, string sheetName, string referenceColumName, string targetColumnName, Dictionary<string, int> modifyDic)
{
IWorkbook workbook = null;
ISheet sheet = null;
//文件是否存在
if (File.Exists(fileName))
{
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(file);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(file);
}
}
}
else
{
return;
}
sheet = workbook.GetSheet(sheetName);
var row = sheet.GetRow(0);
var referenceColumindex = row.Where(w => w.StringCellValue == referenceColumName).First()?.ColumnIndex ?? 0;
var targetColumnIndex = row.Where(w => w.StringCellValue == targetColumnName).First()?.ColumnIndex ?? 0;
for (int i = 0; i < sheet.LastRowNum; i++)
{
var srow = sheet.GetRow(i);
//空行不处理
if (sheet.GetRow(i).GetCell(0) != null)
{
var referValue = sheet.GetRow(i).GetCell(referenceColumindex).StringCellValue;
if (modifyDic.ContainsKey(referValue))
{
//向单元格传值,以覆盖对应的单元格数据
sheet.GetRow(i).GetCell(targetColumnIndex).SetCellValue(modifyDic[referValue]);
}
}
};
using (FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
{
workbook.Write(fileStream); //写入到excel并保存
}
}
/// <summary>
/// Excel导出成内存流
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public static MemoryStream DataTableToExcel(DataTable data)
{
bool isColumnWritten = true;
int i = 0;
int j = 0;
int count = 0;
IWorkbook workbook = new HSSFWorkbook();
try
{
//添加一个sheet
ISheet sheet = workbook.CreateSheet("Sheet1");
//将数据逐步写入sheet1各个行
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
// 写入到客户端
MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return ms;
}
catch (Exception ex)
{
throw ex;
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
/// <summary>
/// 向Excel插入行
/// </summary>
/// <param name="filePath">文件绝对路径</param>
/// <param name="content">插入内容</param>
public static void AddHeadInfo(string filePath, string sheetName, string content)
{
string fileExt = Path.GetExtension(filePath).ToLower();//获取扩展名
IWorkbook workbook;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null) { return; }
ISheet sheet = workbook.GetSheet(sheetName);
//将表格内容整体下移
sheet.ShiftRows(0, sheet.LastRowNum, 1);
var newrow = sheet.CreateRow(0);
ICellStyle style = workbook.CreateCellStyle();//创建单元格样式
style.WrapText = true;//设置换行这个要先设置
newrow.CreateCell(0);
newrow.Cells[0].SetCellValue(content);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
IFont font = workbook.CreateFont();//创建字体样式
font.Color = HSSFColor.Red.Index;//设置字体颜色
if (fileExt == ".xlsx")
{
newrow.HeightInPoints = 20;
font.FontHeight = 14;
}
else
{
newrow.HeightInPoints = 20;
font.FontHeight = 280;
}
style.SetFont(font);//设置单元格样式中的字体样式
newrow.Cells[0].CellStyle = style;//为单元格设置显示样式
sheet.AutoSizeColumn(0);
FileStream out2 = new FileStream(filePath, FileMode.Create);
workbook.Write(out2);
out2.Close();
}
}
/// <summary>
/// Excel导出成内存流
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public static MemoryStream DataTableToExcel(List<DataTable> dtList, List<string> nameList)
{
IWorkbook workbook = new HSSFWorkbook();
try
{
var data = dtList[0];
for (var i = 0; i < dtList.Count(); i++)
{
ISheet sheet = string.IsNullOrWhiteSpace(nameList[i]) ? workbook.CreateSheet("Sheet" + (i + 1)) : workbook.CreateSheet(nameList[i]);
WriteSheet(dtList[i], sheet);
}
// 写入到客户端
MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return ms;
}
catch (Exception ex)
{
throw ex;
return null;
}
}
/// <summary>
/// CSV转换成DataTable（文件流方式）
/// </summary>
/// <param name="csvPath">csv文件路径</param>
/// <returns></returns>
public static DataTable CSVToDataTableByStreamReader(string csvPath)
{
DataTable csvdt = new DataTable("csv");
int intColCount = 0;
bool blnFlag = true;
DataColumn column;
DataRow row;
string strline = null;
string[] aryline;
using (StreamReader reader = new StreamReader(csvPath, GetFileEncoding(csvPath)))
{
while (!string.IsNullOrEmpty((strline = reader.ReadLine())))
{
aryline = strline.Split(new char[] { ',' });
if (blnFlag)
{
blnFlag = false;
intColCount = aryline.Length;
for (int i = 0; i < aryline.Length; i++)
{
column = new DataColumn(aryline[i].Trim('"').Trim('=').Replace("\"", "").Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace("'", "").Replace(" ", "").Trim());
csvdt.Columns.Add(column);
}
continue;
}
row = csvdt.NewRow();
for (int i = 0; i < intColCount; i++)
{
row[i] = aryline[i].Trim('"').Trim('=').Replace("\"", "").Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace("'", "").Trim();
}
csvdt.Rows.Add(row);
}
}
return csvdt;
}
private static Encoding GetFileEncoding(string csvPath)
{
Encoding r;
using (FileStream fs = new FileStream(csvPath, FileMode.Open, FileAccess.Read))
{
r = GetType(fs);
}
return r;
}
/// <summary>
/// DataTable 生成 CSV
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="csvPath">csv文件路径</param>
public static void DataTableToCSV(DataTable dt, string csvPath)
{
if (null == dt)
return;
StringBuilder csvText = new StringBuilder();
StringBuilder csvrowText = new StringBuilder();
foreach (DataColumn dc in dt.Columns)
{
csvrowText.Append(",");
csvrowText.Append(dc.ColumnName);
}
csvText.AppendLine(csvrowText.ToString().Substring(1));
foreach (DataRow dr in dt.Rows)
{
csvrowText = new StringBuilder();
foreach (DataColumn dc in dt.Columns)
{
csvrowText.Append(",");
csvrowText.Append(dr[dc.ColumnName].ToString().Replace(',', ' '));
}
csvText.AppendLine(csvrowText.ToString().Substring(1));
}
File.WriteAllText(csvPath, csvText.ToString(), Encoding.Default);
}
/// <summary>
/// 给定文件的路径，读取文件的二进制数据，判断文件的编码类型
/// </summary>
/// <param name=“FILE_NAME“>文件路径</param>
/// <returns>文件的编码类型</returns>
public static System.Text.Encoding GetType(string FILE_NAME)
{
FileStream fs = new FileStream(FILE_NAME, FileMode.Open, FileAccess.Read);
Encoding r = GetType(fs);
fs.Close();
return r;
}
/// <summary>
/// 通过给定的文件流，判断文件的编码类型
/// </summary>
/// <param name=“fs“>文件流</param>
/// <returns>文件的编码类型</returns>
public static System.Text.Encoding GetType(FileStream fs)
{
byte[] Unicode = new byte[] { 0xFF, 0xFE, 0x41 };
byte[] UnicodeBIG = new byte[] { 0xFE, 0xFF, 0x00 };
byte[] UTF8 = new byte[] { 0xEF, 0xBB, 0xBF }; //带BOM
Encoding reVal = Encoding.Default;
BinaryReader r = new 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 = Encoding.UTF8;
}
else if (ss[0] == 0xFE && ss[1] == 0xFF && ss[2] == 0x00)
{
reVal = Encoding.BigEndianUnicode;
}
else if (ss[0] == 0xFF && ss[1] == 0xFE && ss[2] == 0x41)
{
reVal = Encoding.Unicode;
}
r.Close();
return reVal;
}
/// <summary>
/// 判断是否是不带 BOM 的 UTF8 格式
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
private static 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;
}
private static void WriteSheet(DataTable data, ISheet sheet, bool isColumnWritten = true)
{
int i = 0;
int j = 0;
int count = 0;
//将数据逐步写入sheet1各个行
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
}
/// <summary>
/// 去除空行
/// </summary>
/// <param name="dtr"></param>
/// <returns></returns>
protected static DataTable RemoveEmpty(DataTable dtr)
{
DataTable dt = dtr;
List<DataRow> removelist = new List<DataRow>();
for (int i = 0; i < dt.Rows.Count; i++)
{
bool IsNull = true;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
{
IsNull = false;
}
}
if (IsNull)
{
removelist.Add(dt.Rows[i]);
}
}
for (int i = 0; i < removelist.Count; i++)
{
dt.Rows.Remove(removelist[i]);
}
return dt;
}
}
}
