c# Datatable (2) console, Excel reading and output

1. Console output

Code

#region output DataTable
public static void print_DT(DataTable dt)
{<!-- -->
    if (dt.Rows.Count > 0)
    {<!-- -->
        //Print all column names
        string columnName = string.Empty;
        //Print short lines
        string line_str="",line = "-";
        int long_number = 40;//Fixed length of each column
        //Print column names and dashes
        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);

        //Print the data of each row
        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

Results

2. excel

Instructions for use

1. First add the NuGet package
   
2. Install the third party as shown below
   
   
   After configuring the above, you can use the following code

1. Read excel

Code

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);//Read the first sheet

            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. Output excel

Code

#region output to excel
public static void dt_To_Excel(DataTable dt, bool isShow_Excel)
{<!-- -->
    int row_number = dt.Rows.Count;//Get the number of dt rows
    int col_number = dt.Columns.Count;//Get the number of dt columns
    int col_Index = 0;

    //Create Excel object
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    excel.Application.Workbooks.Add(true);

    //In this way, only one sheet will be opened, and there will not be the situation where two sheets are opened as mentioned below: one is blank and one has data.
    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;

    //Generate field name
    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();
    }

    //Write to Excel
    range = excel.get_Range((object)worksheet.Cells[2, 1], (object)worksheet.Cells[row_number + 1, col_number]);
    range.NumberFormat = "@";//Set the cell to text format
    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 If you comment this block, excel will not be automatically closed.
    //Close the flipbook
    workbook.Close(false, Type.Missing, Type.Missing);
    //Exit Excel
    excel.Quit();
    //Release Excel resources
  System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
    workbook = null;
    worksheet = null;
    range = null;
    excel = null;
    GC.Collect();
    #endregion

}

#endregion

Results