c# Datatable（二）控制台、Excel读取和输出

一、控制台输出

代码

#region 输出 DataTable
public static void print_DT(DataTable dt)
{
    if (dt.Rows.Count > 0)
    {
        //打印所有列名
        string columnName = string.Empty;
        //打印短线
        string line_str="",line = "-";
        int long_number = 40;//每列固定长度
        //打印列名和短线
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            columnName += dt.Columns[i].ColumnName.PadLeft(long_number, ' ') + " | ";
            line_str += line.PadLeft(long_number, '-') + " | ";
        }
        Console.WriteLine(columnName);
        Console.WriteLine(line_str);

        //打印每一行的数据
        foreach (DataRow row in dt.Rows)
        {
            string columnStr = string.Empty;
            foreach (DataColumn column in dt.Columns)
            {
                columnStr += row[column].ToString().PadLeft(long_number, ' ') + " | ";
            }
            Console.WriteLine(columnStr);
        }
    }

}
#endregion

结果

二、excel

使用说明

1. 先添加NuGet程序包
   
2. 安装下图第三方
   
   
   上面配置完，即可使用下面的代码

1、读取excel

代码

public static System.Data.DataSet Get_Excel_Data(string excelFilePath)
{
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Sheets sheets;
    Microsoft.Office.Interop.Excel.Workbook workbook = null;
    object oMissiong = System.Reflection.Missing.Value;

    DataSet dataSet = new DataSet();
    string cellContent;
    try
    {
        if (excel == null)
        {
            return null;
        }
        workbook = excel.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
            oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);

        sheets = workbook.Worksheets;
        for (int p = 1; p <= sheets.Count; p++)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(p);//读取第一张表

            for (int j = 1; j <= workSheet.UsedRange.Columns.Count; j++)
            {

                Range _range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[1, j];
                if (_range.Text.ToString().Trim() == "")
                    dt.Columns.Add("EQUIPMENT");
                else
                    dt.Columns.Add(_range.Text.ToString().Trim());

            }
            for (int i = 2; i <= workSheet.UsedRange.Rows.Count; i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = 1; j <= workSheet.UsedRange.Columns.Count; j++)
                {
                    Range _range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[i, j];
                    cellContent = (_range.Value2 == null) ? "" : _range.Text.ToString().Trim();
                    dr[j - 1] = cellContent;
                }
                dt.Rows.Add(dr);
            }
            dataSet.Tables.Add(dt);
        }
    }
    finally
    {
        workbook.Close(false, oMissiong, oMissiong);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
        workbook = null;
        excel.Workbooks.Close();
        excel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        excel = null;
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
    return dataSet;
}

2、输出excel

代码

#region 输出到excel
public static void dt_To_Excel(DataTable dt, bool isShow_Excel)
{
    int row_number = dt.Rows.Count;//获取dt行数
    int col_number = dt.Columns.Count;//获取dt列数
    int col_Index = 0;

    //建立Excel对象 
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    excel.Application.Workbooks.Add(true);

    //这样就只会打开一个sheet，不会出现下面说到的打开两个sheet：一个空白，一个有数据的情况
    Microsoft.Office.Interop.Excel.Workbook workbook = excel.ActiveWorkbook;
    Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;
    excel.Visible = isShow_Excel;

    Microsoft.Office.Interop.Excel.Range range;

    //生成字段名称 
    foreach (DataColumn col in dt.Columns)
    {
        col_Index++;
        excel.Cells[1, col_Index] = col.ColumnName;
    }

    object[,] objData = new object[row_number, col_number];

    for (int r = 0; r < row_number; r++)
    {
        for (int c = 0; c < col_number; c++)
        {
            objData[r, c] = dt.Rows[r][c];
        }
        //Application.DoEvents();
    }

    // 写入Excel
    range = excel.get_Range((object)worksheet.Cells[2, 1], (object)worksheet.Cells[row_number + 1, col_number]);
    range.NumberFormat = "@";//设置单元格为文本格式
    range.Value2 = objData;
    excel.get_Range((object)worksheet.Cells[2, 1], (object)worksheet.Cells[row_number + 1, 1]).NumberFormat = "yyyy-m-d h:mm";

    #region 此块注释，则不会自动关闭excel
    //关闭活页簿
    workbook.Close(false, Type.Missing, Type.Missing);
    //退出Excel
    excel.Quit();
    //释放Excel资源
  	System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
    workbook = null;
    worksheet = null;
    range = null;
    excel = null;
    GC.Collect();
    #endregion

}

#endregion

结果