Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Import Excel file to C# and export

excel

This topic has been archived. This means that you cannot reply to this topic.
6 replies to this topic

#1 MantasZmnck

MantasZmnck

    CC Lurker

  • New Member
  • Pip
  • 3 posts

Posted 02 December 2014 - 07:55 AM

Hello,

 

I would like to import excel file (excel1.xls) to C# and export as (excel2.xls)

 

Can anyone can help me there? 

 

Excel1 - http://www.stak.lt/excel1.xls

Excel2 - http://www.stak.lt/excel2.xls

 

Thanks in advice.. my e-mail: mantas@stak.lt

 

 


Any programmer please contact me on e-mail: mantas@stak.lt



#2 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts

Posted 02 December 2014 - 08:23 AM

What have you done so far?


My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#3 MantasZmnck

MantasZmnck

    CC Lurker

  • New Member
  • Pip
  • 3 posts

Posted 02 December 2014 - 08:30 AM

I have imported Excel file to Datagrid

 

And now I need regroup this list and export to excel.

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Windows.Documents;
using System.Windows.Controls;
using ADOX;
namespace Import
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        public static string SelectedTable = string.Empty;
 
        private void button1_Click_1(object sender, EventArgs e)
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Title = "Select file";
            fdlg.InitialDirectory = @"c:\";
            fdlg.FileName = txtFileName.Text;
            fdlg.Filter = "Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*";
            fdlg.FilterIndex = 1;
            fdlg.RestoreDirectory = true;
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = fdlg.FileName;
                Import();
                Application.DoEvents();
            }
        }
 
        private void Import()
        {
            if (txtFileName.Text.Trim() != string.Empty)
            {
                try
                {
                    string[] strTables = GetTableExcel(txtFileName.Text);
 
                    frmSelectTables objSelectTable = new frmSelectTables(strTables);
                    objSelectTable.ShowDialog(this);
                    objSelectTable.Dispose();
                    if ((SelectedTable != string.Empty) && (SelectedTable != null))
                    {
                        DataTable dt = GetDataTableExcel(txtFileName.Text, SelectedTable);
                        dataGridView1.DataSource = dt.DefaultView;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }
        }
 
        public static DataTable GetDataTableExcel(string strFileName, string Table)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";");
            conn.Open();
            string strQuery = "SELECT * FROM [" + Table + "]";
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
            System.Data.DataSet ds = new System.Data.DataSet();
            adapter.Fill(ds);
            return ds.Tables[0];
        }
 
        public static string[] GetTableExcel(string strFileName)
        {
            string[] strTables = new string[100];
            Catalog oCatlog = new Catalog();
            ADOX.Table oTable = new ADOX.Table();
            ADODB.Connection oConn = new ADODB.Connection();
            oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
            oCatlog.ActiveConnection = oConn;
            if (oCatlog.Tables.Count > 0)
            {
                int item = 0;
                foreach (ADOX.Table tab in oCatlog.Tables)
                {
                    if (tab.Type == "TABLE")
                    {
                        strTables[item] = tab.Name;
                        item++;
                    }
                }
            }
            return strTables;
        }
 
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
 
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
           
        }
 
        private void saveFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
 
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
 
        }
 
 
 
    }
}

But now I dont know how to search where is [*priekis] and I need this value 17,43 to add to B1 field...

And so on with another names and values... 

 

Maybe you can contact me on e-mail to help me?



#4 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts

Posted 02 December 2014 - 08:44 AM

First, we don't give help via private message or email here.  That's because you're probably not the only person with a certain problem.  The forum serves as a means for others to find solutions to their problems that have been previously answered.

 

With that said, use the Microsoft Interop:

 

This creates an extension method for Datatables.  

public static class My_DataTable_Extensions
{
    /// <summary>
    /// Export DataTable to Excel file
    /// </summary>
    /// <param name="DataTable">Source DataTable</param>
    /// <param name="ExcelFilePath">Path to result file name</param>
    public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
    {
        try
        {
            int ColumnsCount;

            if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook
            Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbooks.Add();

            // single worksheet
            Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;

            object[] Header = new object[ColumnsCount];

            // column headings               
            for (int i = 0; i < ColumnsCount; i++)
                Header[i] = DataTable.Columns[i].ColumnName;

            Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
            HeaderRange.Value = Header;
            HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            HeaderRange.Font.Bold = true;

            // DataCells
            int RowsCount = DataTable.Rows.Count;
            object[,] Cells = new object[RowsCount, ColumnsCount];

            for (int j = 0; j < RowsCount; j++)
                for (int i = 0; i < ColumnsCount; i++)
                    Cells[j, i] = DataTable.Rows[j][i];

            Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;

            // check fielpath
            if (ExcelFilePath != null && ExcelFilePath != "")
            {
                try
                {
                    Worksheet.SaveAs(ExcelFilePath);
                    Excel.Quit();
                    System.Windows.MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                        + ex.Message);
                }
            }
            else    // no filepath is given
            {
                Excel.Visible = true;
            }
        }
        catch (Exception ex)
        {
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }
}

You would call it, simply by 

DataTable dt = GetDataTableExcel(txtFileName.Text, SelectedTable);

dt.ExportToExcel( yourOutputFileLocation );

My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#5 MantasZmnck

MantasZmnck

    CC Lurker

  • New Member
  • Pip
  • 3 posts

Posted 02 December 2014 - 08:47 AM

Can you contact me?

 

I could pay for this application.. mantas@stak.lt 

 

Thank you.



#6 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts

Posted 02 December 2014 - 08:48 AM

See my previous post about private message/ email.


My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#7 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts

Posted 02 December 2014 - 10:58 PM

Why don't you take a look to Codecall's export to excel from datatables tutorial ?






Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download