C#创建和获取.csv文件

最新推荐文章于 2024-08-29 07:36:18 发布

置顶

女盆友——ZY

最新推荐文章于 2024-08-29 07:36:18 发布

阅读量2.3k

点赞数 1

分类专栏：工作中学会问题

本文链接：https://blog.csdn.net/qq_44858151/article/details/110920118

版权

本文介绍了如何在C#中创建和获取.csv文件，包括调用方法和创建专门的方法类，详细阐述了创建文件和读取文件的步骤。

摘要由CSDN通过智能技术生成

1.调用方法

（1）创建.csv文件

bool Filebool = FileUtil.OutPutCsvFile(DATATABLE表, "A:\XXX.csv", false, false);

（2）获取.csv文件

string csvDirectory="A:\\";
DirectoryInfo dicsv = new DirectoryInfo(csvDirectory);
FileInfo[] fiscsv = dicsv.GetFiles( "XXX"+"*.csv");
	DataTable dt = FileUtil.OpenCSVFile2DataTable(fiscsv[0].FullName, "", true);

2.创建方法类

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Reflection;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;


namespace DMYH_BP_203RPEDevice
{
    public static class FileUtil
    {
        /// <summary>
        /// 将Excel导入DataSet
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="sheetName">页名</param>
        /// <returns>如果返回null表示错误</returns>
        public static DataSet ExceltToDS(string path, string sheetName, string OfficeVersion)
        {
            string strConn = "";
            string strExcel = "";

        OleDbConnection conn = null;
        OleDbDataAdapter adapter = null;
        DataSet ds = null;

        //string strOfficeVersion = LocalConfig.IniReadValue("OfficeVersions", OfficeVersion);//配置Excel版本
        //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
        //if (strOfficeVersion.Equals(EnumOfficeVersions.office2007或更高版本.ToString("d")))
        //{
        //    strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
        //}
        strExcel = "Select * from [" + sheetName + "$]";

        try
        {
            conn = new OleDbConnection(strConn);
            conn.Open();

            adapter = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            adapter.Fill(ds, "myTable");
            return ds;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            try
            {
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
    /// <summary>
    /// 读取excle文件中的表单名称，在返回的table中字段名称为"TABLE_NAME"
    /// </summary>
    /// <param name="file"></param>
    /// <returns></returns>
    public static DataTable getExcelSheetNames(string file, string OfficeVersion)
    {
        OleDbConnection conn = null;
        //string strOfficeVersion = LocalConfig.IniReadValue("OfficeVersions", OfficeVersion);
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";
        //if (strOfficeVersion.Equals(EnumOfficeVersions.office2007或更高版本.ToString("d")))
        //{
            strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
        //}

        try
        {
            conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            return table;
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            try
            {
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

    /// <summary>
    /// 创建excel文件
    /// </summary>
    /// <param name="dtSource"></param>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public static bool CreateExcleWithFormatFromDataTable(System.Data.DataTable dtSource, string filePath)
    {
        Microsoft.Office.Interop.Excel.Application app = null;
        Microsoft.Office.Interop.Excel.Workbook wb = null;
        Microsoft.Office.Interop.Excel.Worksheet sheet = null;

        try
        {
            app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;

            wb = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Add(Missing.Value);
            sheet = wb.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

            if (dtSource.Rows.Count > 0)
            {
                int row = 0;
                row = dtSource.Rows.Count;
                int col = dtSource.Columns.Count;
                for (int i = 0; i < row; i++)
                {
                    for (int j = 0; j < col; j++)
                    {
                        string str = dtSource.Rows[i][j].ToString();
                        sheet.Cells[i + 2, j + 1] = str;
                    }
                }
            }

            int size = dtSource.Columns.Count;
            for (int i = 0; i < size; i++)
            {
                sheet.Cells[1, 1 + i] = dtSource.Columns[i].ColumnName;
                //设置自动列宽
                Microsoft.Office.Interop.Excel.Range allColumn = sheet.Columns;
                allColumn.AutoFit();
            }
            wb.Saved = true;

            app.ActiveWorkbook.SaveCopyAs(filePath);
            return true;
        }
        finally
        {
            wb.Close(null, null, null);
            app.Workbooks.Close();
            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)app);
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)wb);
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet);
            GC.Collect();
        }

        //object MissingValue = Type.Missing;
        //Microsoft.Office.Interop.Excel.Application app =
        //    new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook wbook =
            app.Workbooks.Open(filePath, MissingValue, MissingValue
            , MissingValue
            , MissingValue, MissingValue, MissingValue, MissingValue
            , MissingValue, MissingValue, MissingValue, MissingValue
            , MissingValue, MissingValue, MissingValue);
        Microsoft.Office.Interop.Excel.Worksheet wSheet =
            wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;


        Microsoft.Office.Interop.Excel.Application app =
            new Microsoft.Office.Interop.Excel.Application();
        app.Visible = false;
        //Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Add(true);
        //Microsoft.Office.Interop.Excel.Worksheet wSheet = wbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

        //if (dtSource.Rows.Count > 0)
        //{
        //    int row = 0;
        //    row = dtSource.Rows.Count;
        //    int col = dtSource.Columns.Count;
        //    for (int i = 0; i < row; i++)
        //    {
        //        for (int j = 0; j < col; j++)
        //        {
        //            string str = dtSource.Rows[i][j].ToString();
        //            wSheet.Cells[i + 2, j + 1] = str;
        //        }
        //    }
        //}

        //int size = dtSource.Columns.Count;
        //for (int i