C# on the implementation of converting Excel files to DataTable and TXT text

C# about the implementation of converting Excel files to DataTable and TXT text

FileToDataTable

 /// <summary>
        ///
        /// </summary>
        /// <param name="fileName">File path Path + FileName</param>
        /// <param name="sheetName">Page signature</param>
        /// <returns></returns>
        private DataTable FileToDataTable(string fileName, string sheetName)
        {
            DataSet dst = new DataSet();
            DataTable dt = new DataTable();

            dt = XlsToDataTable(fileName, sheetName);

            return dt;
        }

XlsToDataTable

Need to reference NPOL.dll

 public DataTable XlsToDataTable(string vFilePath, string vSheetName)
        {
            DataTable dataTable = new DataTable();
            Stream stream = null;
            try
            {
                stream = File.OpenRead(vFilePath);
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
                HSSFSheet hssfsheet = (HSSFSheet)hssfworkbook.GetSheet(vSheetName);
                HSSFRow hssfrow = (HSSFRow)hssfsheet.GetRow(0);
                int lastCellNum = (int)hssfrow.LastCellNum;
                for (int i = (int)hssfrow.FirstCellNum; i < lastCellNum; i + + )
                {
                    DataColumn column = new DataColumn(hssfrow.GetCell(i).StringCellValue);
                    dataTable.Columns.Add(column);
                }
                dataTable.TableName = vSheetName;
                int lastRowNum = hssfsheet.LastRowNum;
                for (int i = hssfsheet.FirstRowNum + 1; i <= hssfsheet.LastRowNum; i + + )
                {
                    HSSFRow hssfrow2 = (HSSFRow)hssfsheet.GetRow(i);
                    DataRow dataRow = dataTable.NewRow();
                    for (int j = (int)hssfrow2.FirstCellNum; j < lastCellNum; j + + )
                    {
                        dataRow[j] = hssfrow2.GetCell(j).ToString();
                    }
                    dataTable.Rows.Add(dataRow);
                }
                stream.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Xls to DataTable: \
" + ex.Message);
            }
            finally
            {
                if (stream != null)
                {
                    stream.Close();
                }
            }
            return dataTable;
        }

Another way:

/*
     *Quoting the NuGet package Spire.XLS
     */
    /// <summary>
    /// Excel help class
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// Convert Excel to DataTable with file stream
        /// </summary>
        /// <param name="hasTitle">Whether there is a header</param>
        /// <param name="path">File path</param>
        /// <param name="tableindex">File book index</param>
        public DataTable ExcelToDataTableFormPath(bool hasTitle = true, string path = "", int tableindex = 0)
        {
            //New Workbook
            Workbook workbook = new Workbook();
            //Read the file contents under the current path into the workbook object
            workbook.LoadFromFile(path);
            //Get the first Sheet page
            Worksheet sheet = workbook.Worksheets[tableindex];
            return SheetToDataTable(hasTitle, sheet);
        }
        /// <summary>
        /// Convert Excel to DataTable with file stream
        /// </summary>
        /// <param name="hasTitle">Whether there is a header</param>
        /// <param name="stream">File stream</param>
        /// <param name="tableindex">File book index</param>
        public DataTable ExcelToDataTableFormStream(bool hasTitle = true, Stream stream = null, int tableindex = 0)
        {
            //New Workbook
            Workbook workbook = new Workbook();
            //Read the file stream content into the workbook object
            workbook.LoadFromStream(stream);
            //Get the first Sheet page
            Worksheet sheet = workbook.Worksheets[tableindex];
            return SheetToDataTable(hasTitle, sheet);
        }

        private DataTable SheetToDataTable(bool hasTitle, Worksheet sheet)
        {
            int iRowCount = sheet.Rows.Length;
            int iColCount = sheet.Columns.Length;
            DataTable dt = new DataTable();
            //Generate column headers
            for (int i = 0; i < iColCount; i + + )
            {
                var name = "column" + i;
                if(hasTitle)
                {
                    var txt = sheet.Range[1, i + 1].Text;
                    if (!string.IsNullOrEmpty(txt)) name = txt;
                }
                while (dt.Columns.Contains(name)) name = name + "_1";//An error will be reported if the row name is repeated.
                dt.Columns.Add(new DataColumn(name, typeof(string)));
            }
            //Generate row data
            int rowIdx = hasTitle ? 2 : 1;
            for (int iRow = rowIdx; iRow <= iRowCount; iRow + + )
            {
                DataRow dr = dt.NewRow();
                for (int iCol = 1; iCol <= iColCount; iCol + + )
                {
                    dr[iCol - 1] = sheet.Range[iRow, iCol].Text;
                }
                dt.Rows.Add(dr);
            }
            return RemoveEmpty(dt);
        }

        /// <summary>
        /// Remove blank lines
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private DataTable RemoveEmpty(DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i + + )
            {
                bool rowdataisnull = true;
                for (int j = 0; j < dt.Columns.Count; j + + )
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {
                        rowdataisnull = false;
                    }
                }
                if (rowdataisnull)
                {
                    removelist.Add(dt.Rows[i]);
                }
            }
            for (int i = 0; i < removelist.Count; i + + )
            {
                dt.Rows.Remove(removelist[i]);
            }
            return dt;
        }
    }

DataTableToTxt

 public object DataTableToTxt(DataTable vContent, string vOutputFilePath)
        {
            object resObj;
            StringBuilder sTxtContent;

            try
            {
                if (File.Exists(vOutputFilePath))
                    File.Delete(vOutputFilePath);

                sTxtContent = new StringBuilder();

                //data
                foreach (DataRow row in vContent.Rows)
                {
                    for (int i = 0; i < vContent.Columns.Count; i + + )
                    {
                        sTxtContent.Append(row[i].ToString().Trim());
                        sTxtContent.Append(i == vContent.Columns.Count - 1 ? "\r\
" : "\t");
                    }
                }
                File.WriteAllText(vOutputFilePath, sTxtContent.ToString(), Encoding.Unicode);
                resObj = new object[] { 0, "OK" };
            }
            catch (Exception ex)
            {
                resObj = new object[] { 0, "OK" };
            }
            return resObj;
        }