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; }