locked
Import into excel from text file RRS feed

  • Question

  • Hi,

    In have my .net code that generates a standard excel template for any table depending upon table schema. Now I want to import the data of that table from database to this excel template. Since the data is bulky I cannot use any dataset or tables to get data from database.

    Thursday, May 9, 2013 9:35 AM

Answers

  • I have found exporting using XML from C# that the latest Excel versions can understand; is very helpful. It is blazingly fast.

    Viral.


    MCTS - WPF, WinForms, Sql Server 2008

    • Proposed as answer by Nitinrpatel Thursday, May 9, 2013 12:56 PM
    • Marked as answer by Bob Shen Friday, May 31, 2013 10:10 AM
    Thursday, May 9, 2013 10:02 AM
  • another Way , you can do sirectly with Excel

    Refer Article

    Regards

    Mohit Gupta

    • Marked as answer by Bob Shen Friday, May 31, 2013 10:10 AM
    Thursday, May 9, 2013 11:51 AM
  • http://www.extendoffice.com/documents/excel/806-excel-import-text-worksheet-file.html


     private void Convert_TextFile_To_ExcelFile()
            {
                // Open the text file in Excel.
                m_objExcel = new Microsoft.Office.Interop.Excel.Application();
                m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
                m_objBooks.OpenText(m_strSampleFolder + "sampledoc.txt", 
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1,
                    Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited,
                    Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
                    false, true, false, false, false, false, m_objOpt, m_objOpt,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

                m_objBook = m_objExcel.ActiveWorkbook;

                // Save the text file in the typical workbook format and quit Excel.
                   m_objBook.SaveAs(m_strSampleFolder + "sampledoc.xls", 
                    Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                m_objBook.Close(false, m_objOpt, m_objOpt);
                m_objExcel.Quit();

            }

    http://support.microsoft.com/kb/306023

    private void Convert_TextFile_To_ExcelFile() { // Open the text file in Excel. m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBooks.OpenText(m_strSampleFolder + "sampledoc.txt", Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1, Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook = m_objExcel.ActiveWorkbook; // Save the text file in the typical workbook format and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "sampledoc.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); }
    private void Convert_TextFile_To_ExcelFile() { // Open the text file in Excel. m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBooks.OpenText(m_strSampleFolder + "sampledoc.txt", Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1, Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook = m_objExcel.ActiveWorkbook; // Save the text file in the typical workbook format and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "sampledoc.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); }
    private void Convert_TextFile_To_ExcelFile() { // Open the text file in Excel. m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBooks.OpenText(m_strSampleFolder + "sampledoc.txt", Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1, Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook = m_objExcel.ActiveWorkbook; // Save the text file in the typical workbook format and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "sampledoc.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); }

    As Kushwaha

    • Marked as answer by Bob Shen Friday, May 31, 2013 10:10 AM
    Thursday, May 9, 2013 12:45 PM

All replies

  • I have found exporting using XML from C# that the latest Excel versions can understand; is very helpful. It is blazingly fast.

    Viral.


    MCTS - WPF, WinForms, Sql Server 2008

    • Proposed as answer by Nitinrpatel Thursday, May 9, 2013 12:56 PM
    • Marked as answer by Bob Shen Friday, May 31, 2013 10:10 AM
    Thursday, May 9, 2013 10:02 AM
  • another Way , you can do sirectly with Excel

    Refer Article

    Regards

    Mohit Gupta

    • Marked as answer by Bob Shen Friday, May 31, 2013 10:10 AM
    Thursday, May 9, 2013 11:51 AM
  • http://www.extendoffice.com/documents/excel/806-excel-import-text-worksheet-file.html


     private void Convert_TextFile_To_ExcelFile()
            {
                // Open the text file in Excel.
                m_objExcel = new Microsoft.Office.Interop.Excel.Application();
                m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
                m_objBooks.OpenText(m_strSampleFolder + "sampledoc.txt", 
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1,
                    Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited,
                    Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
                    false, true, false, false, false, false, m_objOpt, m_objOpt,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

                m_objBook = m_objExcel.ActiveWorkbook;

                // Save the text file in the typical workbook format and quit Excel.
                   m_objBook.SaveAs(m_strSampleFolder + "sampledoc.xls", 
                    Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                m_objBook.Close(false, m_objOpt, m_objOpt);
                m_objExcel.Quit();

            }

    http://support.microsoft.com/kb/306023

    private void Convert_TextFile_To_ExcelFile() { // Open the text file in Excel. m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBooks.OpenText(m_strSampleFolder + "sampledoc.txt", Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1, Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook = m_objExcel.ActiveWorkbook; // Save the text file in the typical workbook format and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "sampledoc.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); }
    private void Convert_TextFile_To_ExcelFile() { // Open the text file in Excel. m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBooks.OpenText(m_strSampleFolder + "sampledoc.txt", Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1, Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook = m_objExcel.ActiveWorkbook; // Save the text file in the typical workbook format and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "sampledoc.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); }
    private void Convert_TextFile_To_ExcelFile() { // Open the text file in Excel. m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBooks.OpenText(m_strSampleFolder + "sampledoc.txt", Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1, Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook = m_objExcel.ActiveWorkbook; // Save the text file in the typical workbook format and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "sampledoc.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); }

    As Kushwaha

    • Marked as answer by Bob Shen Friday, May 31, 2013 10:10 AM
    Thursday, May 9, 2013 12:45 PM