[common methods] 2. Export and import between datatable (or DataSet) and Excel file (using NPOI)

Mutual guide between DataTable and Excel

1. Add NPOI reference to the project

 

  • NPOI Project Profile:
NPOI is an open-source project of reading and writing Microsoft OLE2 component documents such as Excel and word. It can read and write word or EX C EL documents without installing Office. It also supports the old version of Office (2003 -) and the new version of Office(2007 +). NPOI is very powerful and can operate all details of Excel or word documents. If you want to study NPOI in detail, there are special books on Taobao to talk about NPOI. Of course, you can also visit the official website of NPOI to view their documents and Demo.
  • About Excel extensions. xls and. xlsx:
. xls is the file format saved by Excel2003 and earlier versions. The old version and the new version of Excel are compatible with it. xlsx is the default file format saved by Excel2007 and later versions of Excel. This format can only be compatible with Excel2007 and later versions of Excel. The old version of Excel cannot be opened.
  • References to NPOI projects:

Using visual studio, open the NuGet package manager, search for NPOI, and add it to the current project.

  

 

 

 

2. Import Excel file into DataSet

 

Because an excel file can have multiple sheet pages, the C ා method is best to use the DataSet class corresponding to the excel document structure, that is, the DataSet (which can contain multiple DataTable s) is exported to excel, and each datatab in the DataSet corresponds to a sheet page in Excel.

  

Method explanation: this method imports an excel file into a DataSet, and each Sheet page in Excel will generate a DataTable to be stored in the imported DataSet, and the TableName of each datatable corresponds to the SheetName of each Sheet one by one. The parameter isFirstLineColumnName is used to indicate whether the first row of each table in the excel file is a header.

The code is as follows:

        /// <summary>
        /// read Execl Data to DataTable(DataSet)in
        /// </summary>
        /// <param name="filePath">Appoint Execl File path</param>
        /// <param name="isFirstLineColumnName">Set whether the first row is a column name</param>
        /// <returns>Return to one DataTable data set</returns>
        public static DataSet ExcelToDataSet(string filePath, bool isFirstLineColumnName)
        {
            DataSet dataSet = new DataSet();
            int startRow = 0;
            try
            {
                using (FileStream fs = File.OpenRead(filePath))
                {
                    IWorkbook workbook = null;
                    // If it's 2007+Of Excel Edition
                    if (filePath.IndexOf(".xlsx") > 0)
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    // If it's 2003-Of Excel Edition
                    else if (filePath.IndexOf(".xls") > 0)
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    if (workbook != null)
                    {
                        //Cyclic reading Excel Each sheet,each sheet Convert all pages to one DataTable,And put it on DataSet in
                        for (int p = 0; p < workbook.NumberOfSheets; p++)
                        {
                            ISheet sheet = workbook.GetSheetAt(p);
                            DataTable dataTable = new DataTable();
                            dataTable.TableName = sheet.SheetName;
                            if (sheet != null)
                            {
                                int rowCount = sheet.LastRowNum;//Get total rows
                                if (rowCount > 0)
                                {
                                    IRow firstRow = sheet.GetRow(0);//Get the first line
                                    int cellCount = firstRow.LastCellNum;//Get total columns

                                    //structure datatable Column
                                    if (isFirstLineColumnName)
                                    {
                                        startRow = 1;//If the first row is a column name, read from the second row
                                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                        {
                                            ICell cell = firstRow.GetCell(i);
                                            if (cell != null)
                                            {
                                                if (cell.StringCellValue != null)
                                                {
                                                    DataColumn column = new DataColumn(cell.StringCellValue);
                                                    dataTable.Columns.Add(column);
                                                }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                        {
                                            DataColumn column = new DataColumn("column" + (i + 1));
                                            dataTable.Columns.Add(column);
                                        }
                                    }

                                    //Fill row
                                    for (int i = startRow; i <= rowCount; ++i)
                                    {
                                        IRow row = sheet.GetRow(i);
                                        if (row == null) continue;

                                        DataRow dataRow = dataTable.NewRow();
                                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                                        {
                                            ICell cell = row.GetCell(j);
                                            if (cell == null)
                                            {
                                                dataRow[j] = "";
                                            }
                                            else
                                            {
                                                //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                                switch (cell.CellType)
                                                {
                                                    case CellType.Blank:
                                                        dataRow[j] = "";
                                                        break;
                                                    case CellType.Numeric:
                                                        short format = cell.CellStyle.DataFormat;
                                                        //For time format (2015.12.5,2015/12/5,2015-12-5 Etc.)
                                                        if (format == 14 || format == 31 || format == 57 || format == 58)
                                                            dataRow[j] = cell.DateCellValue;
                                                        else
                                                            dataRow[j] = cell.NumericCellValue;
                                                        break;
                                                    case CellType.String:
                                                        dataRow[j] = cell.StringCellValue;
                                                        break;
                                                }
                                            }
                                        }
                                        dataTable.Rows.Add(dataRow);
                                    }
                                }
                            }
                            dataSet.Tables.Add(dataTable);
                        }

                    }
                }
                return dataSet;
            }
            catch (Exception)
            {
                return null;
            }
        }    

 

3. Export DataSet (DataTable) as an Excel file

 

Code explanation: we can put all the DataTable s to be exported into a DataSet, and export the DataSet to an excel file. The method parameter OutPath is used to pass in the path of the exported excel file. The path can add the extension of Excel (. xls or. xlsx), or not. The extension of the exported excel file is. xls by default.   

        /// <summary>
        /// take DataTable(DataSet)Export to Execl File
        /// </summary>
        /// <param name="dataSet">Introduction of one DataSet</param>
        /// <param name="Outpath">Export path (extension can be omitted, default is.xls)</param>
        /// <returns>Return to one Bool Value of type, indicating whether the export is successful</returns>
        /// True Indicates the export is successful, Flase Indicates export failed
        public static bool DataTableToExcel(DataSet dataSet, string Outpath)
        {
            bool result = false;
            try
            {
                if (dataSet == null || dataSet.Tables == null || dataSet.Tables.Count == 0 || string.IsNullOrEmpty(Outpath))
                    throw new Exception("Input DataSet Or path exception");
                int sheetIndex = 0;
                //Based on the extension of the output path workbook Instance type of
                IWorkbook workbook = null;
                string pathExtensionName = Outpath.Trim().Substring(Outpath.Length - 5);
                if (pathExtensionName.Contains(".xlsx"))
                {
                    workbook = new XSSFWorkbook();
                }
                else if(pathExtensionName.Contains(".xls"))
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    Outpath = Outpath.Trim() + ".xls";
                    workbook = new HSSFWorkbook();
                }
                //take DataSet Export as Excel
                foreach (DataTable dt in dataSet.Tables)
                {
                    sheetIndex++;
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? ("sheet" + sheetIndex) : dt.TableName);//Create a name Sheet0 Table
                        int rowCount = dt.Rows.Count;//Row number
                        int columnCount = dt.Columns.Count;//Column number

                        //Set column head
                        IRow row = sheet.CreateRow(0);//excel Set first row as column header
                        for (int c = 0; c < columnCount; c++)
                        {
                            ICell cell = row.CreateCell(c);
                            cell.SetCellValue(dt.Columns[c].ColumnName);
                        }

                        //Set cells per row and column,
                        for (int i = 0; i < rowCount; i++)
                        {
                            row = sheet.CreateRow(i + 1);
                            for (int j = 0; j < columnCount; j++)
                            {
                                ICell cell = row.CreateCell(j);//excel The second line starts to write data
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }
                    }
                }
                //towards outPath output data
                using (FileStream fs = File.OpenWrite(Outpath))
                {
                    workbook.Write(fs);//Open this xls Write data to file
                    result = true;
                }
                return result;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }

 

4. How to use the above two methods

 

Define the above two methods in the ExcelHelper class, using the following code:

        DataSet set = ExcelHelper.ExcelToDataTable("test.xlsx", true);//Excel import
        bool b = ExcelHelper.DataTableToExcel(set, "test2.xlsx");//Export Excel

Tags: C# Excel

Posted on Sun, 03 Nov 2019 20:40:46 -0800 by christofurr