ExcelTools

ExcelTools

public class ExcelTools
    {
        public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
        {
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;
//            if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";\"";
//            else
//                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";

            DataSet output = new DataSet();

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();

                DataTable schemaTable = conn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                foreach (DataRow schemaRow in schemaTable.Rows)
                {
                    string sheet = schemaRow["TABLE_NAME"].ToString();

                    if (!sheet.EndsWith("_"))
                    {
                        try
                        {
                            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                            cmd.CommandType = CommandType.Text;

                            DataTable outputTable = new DataTable(sheet);
                            output.Tables.Add(outputTable);
                            new OleDbDataAdapter(cmd).Fill(outputTable);
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
                        }
                    }
                }
            }
            return output;
        }

        public static DataSet LoadWithNPOI(string filePath)
        {
            HSSFWorkbook hssfwb;
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                hssfwb = new HSSFWorkbook(file);
            }

            ISheet sheet = hssfwb.GetSheetAt(0);

            DataSet ds = new DataSet();
            ds.Tables.Add(new DataTable("tbl1"));
            for (int row = 0; row <= sheet.LastRowNum; row++)
            {
                /*if (sheet.GetRow(row) != null) //null is when the row only contains empty cells 
                {
                    return string.Format("Row {0} = {1}", row, sheet.GetRow(row).GetCell(0).StringCellValue);
                }*/
                var dr = ds.Tables[0].NewRow();
                int index = 0;
                foreach (var cell in sheet.GetRow(row).Cells)
                {
                    dr[index]=cell.StringCellValue;
                    index++;
                }
                ds.Tables[0].Rows.Add(dr);
            }
            return ds;
        }

        public static DataTable LoadFromXml(string xml)
        {
            var tbl = new DataTable();
            for (int i = 0; i < 26; i++)
            {
                tbl.Columns.Add();
            }
            var elem = XElement.Parse(xml);
            foreach (var row in elem.Descendants().Where(s => s.Name.LocalName == "Row"))
            {
                var r = tbl.NewRow();
                int i = 0;
                foreach (var data in row.Descendants().Where(s => s.Name.LocalName == "Cell"))
                {
                    r[i] = data.Value;
                    i++;
                }
                tbl.Rows.Add(r);
            }
            return tbl;
        }
    }