加载xlsx文件生成csv文件
读取excel文件内容 生成 DataTable
/// <summary>
/// 读取excel文件内容
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="columnNum">行数</param>
/// <param name="rowNum">列数</param>
/// <returns></returns>
public static void ReadExcelDataTable(string filePath, string file)
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
for (int i = 0; i < result.Tables.Count; i++)
{
dataTableToCsv((DataTable)(object)result.Tables[i], file + result.Tables[i].TableName + ".csv");
}
}
根据 DataTable 生成 CSV文件
public static void dataTableToCsv(DataTable table, string file)
{
FileInfo fi = new FileInfo(file);
string path = fi.DirectoryName;
string name = fi.Name;
//\/:*?"<>|
//把文件名和路径分别取出来处理
name = name.Replace(@"\", "\");
name = name.Replace(@"/", "/");
name = name.Replace(@":", ":");
name = name.Replace(@"*", "*");
name = name.Replace(@"?", "?");
name = name.Replace(@"<", "<");
name = name.Replace(@">", ">");
name = name.Replace(@"|", "|");
string title = "";
FileStream fs = new FileStream(path + "\\" + name, FileMode.Create);
StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
foreach (DataRow row in table.Rows)
{
if (row.RowState == DataRowState.Deleted) continue;
string line = "";
int cout = 0;
for (int i = 0; i < table.Columns.Count; i++)
{
string vs = row[i].ToString();
if (string.IsNullOrEmpty(vs))
{
cout++;
}
line += vs.Replace(",", "") + ",";
if (line.Contains("\n")) // 把每个数据的换行替换成&
{
line = line.Replace("\n", "&");
}
}
if(cout == table.Columns.Count)// 识别当前行是不是都是空的 如果是就跳过
{
continue;
}
line = line.Substring(0, line.Length - 1) + "\n";
sw.Write(line);
}
sw.Close();
fs.Close();
}
生成的文件为UTF-8
使用 Encoding.UTF8 是自动带 三 byte 的 BOM,如果要不添加 BOM。应该改用 UTF8Encoding utf8 = new UTF8Encoding(是否添加 BOM);