2021-09-05

读取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);