importing an excel file to datagrid on delphi

Question

I've tried this code on .NET -to import an excel file to devxpress datagrid- and it worked fine but i have to create a procedure doing the same job on delphi now. i dont know much about delphi so i need some ideas how to do that.

   public static DataTable 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 + ";IMEX=0\"";
        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);

                        new OleDbDataAdapter(cmd).Fill(outputTable);

                        if (outputTable.Rows.Count > 0)
                        {
                            return outputTable;
                        }

                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
                    }
                }
            }
        }
        return null;
    } 

Answer 1

You have three main options to interop with Microsoft Excel tables

1) you source uses OLE DB to reach data. This has three main misfeatures:

• Microsoft ADO is deprecated and Delphi ADO library is bugs-ridden. Those bugs have workarounds but you would spend your time detecting them and learning those tricks.
• This needs Microsoft Excel installed and that is not free program
• Spreadsheet is not tabular data, so you would be able to read only most simplisticalyl structured data that way.

But it is fast and convenient, if it works.

• ADO with XLSX files in Delphi XE
• Update cell query for Excel ADO from Delphi
• ADO SQL type cast Float to String (Excel)
• Using ADODataset component to open an Excel Spreadsheet
• Delphi ADO with Excel 2010 or newer, problems with .RecordCount
• How to insert value into the Excel file using ADO?
• Delphi ADO Query
• Delphi - Excel rows get by an ADO Query
• Provider selection, when trying to access Excel table in Delphi 7

You can also read a lot of tutorials and how-tos. For example, using BabelFish or GoogleTranslate you may try http://devdelphi.ru/?p=63

---

2) You can use running Excel application as COM server. You just need to drop ExcelApplication component on your form. See c:\RAD Studio\9.0\OCX\Servers\

That is arguably the most usual way to use Excel. It has few advantages:

• COM interfaces are a natural API for Excel, so you would have access to mopst of its features
• you can take any code sample in Visual Basic or C++ and verbatim translate it to Delphi
• Microsoft has a lot of documentation of that API
• for most actions you can use built-in Excel macro recorder to get a ready-made program, how to do something in Excel
• Usually Excel is meant to be final authority is some file is valid or not. So you can be sure that your program would be able to read any file given. And if not - you can blame Microsoft Excel for it, not your program.
• It is arguably easier to make loop over rows, then over cells, than grasp what is SQL and how to use it.

However

• This needs Microsoft Excel installed and that is not free program
• This is slow. Even using arrays for data transfer
• for non-English locales, there are unfixed bugs in COM API
• There are some bugs in Delphi COM implementation as well.

Again, you have a lot of tutorials about using that component. And you have a lot of Microsoft tutorials about using Visual Basic to control Excel

• International date formatting issues in OLE Excel output
• https://www.google.ru/search?client=opera&q=site:stackoverflow.com+delphi+excelapplication&sourceid=opera

and so one. If you would like to change your approach to this one - you would have a LOT of info

2.1) One can also use OpenOffice.org (run scalc.exe server, controlled via COM or HTTP, just like you do run excel.exe server) to read/write Microsoft Office files and some web-servers do it. But whlie it is possible i did not saw that approach popular. So just mentioning it.

---

3) there are some Delphi-native libraries able to read/write Excel files directly

• That is probably the fastest way, so it rocks when you have a lot of data
• you're in control of what happening - you can always change the library just liek you change your own program
• your program becomes self-contained - it does not need neither Excel itself nor Excel OLE DB drivers installed.

However

• Those libraries always play catch-up with Excel so they support only subset of its features, and usually a rather narrow one
• Even file formats: some libraries support only XLS, some - only XML and XLSX. Especially cheap or free ones.
• Those libraries, just like any program, have their bugs. And you would not be able to say "That is just how Excel works."