Geek asks and answers

The easiest way to insert simple data into an Excel file from .Net

I have an excel file that has ~ 10 columns and 1-20 rows. I need to insert 1-20 rows with various data elements.

I was wondering if there is a way to place some tags in an excel file so that they can be found and replaced. Something that marks a column as "Name". So in code, I could just say:

Name[0] = object.name;

I’m not sure if this exact method is possible, but I really don’t need a heavy lift, and I probably won’t hardcode the cells, as the Excel file may change over time.

I will also need to add a hidden cell "ID" in the row. I guess I can cross this bridge later.

+5
source share
2 answers

ADO.NET Excel

string fileName = @"D:\test.xlsx"; 
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" + 
        "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName); 

using(OleDbConnection cn = new OleDbConnection(connectionString))
{
    cn.Open();
    OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [Sheet1$] " + 
         "([Column1],[Column2],[Column3],[Column4]) " + 
         "VALUES(@value1, @value2, @value3, @value4)", cn);
   cmd1.Parameters.AddWithValue("@value1", "Key1");
   cmd1.Parameters.AddWithValue("@value2", "Sample1");
   cmd1.Parameters.AddWithValue("@value3", 1);
   cmd1.Parameters.AddWithValue("@value4", 9);
   cmd1.ExecuteNonQuery();
}

, Column1... . , ACE OleDB Excel 2007 2010 Microsoft.Jet.OleDb.4.0.

EDIT: sql

    OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [yourNamedRange] " + 
                        "VALUES(@value1, @value2, @value3, @value4)", cn);

, .

+11
    private Excel.Application app = null;
    private Excel.Workbook workbook = null;
    private Excel.Worksheet worksheet = null;
    private Excel.Range workSheet_range = null;
    private const int FIRTSCOLUMN= 0 //Here const you will use to select good column
    private const int FIRSTROW= 0
    private const int FIRSTSHEET= 1

    app = new Excel.Application();
    app.Visible = true;
    workbook = app.Workbooks.Add(1);
    worksheet = (Excel.Worksheet)workbook.Sheets[FIRSTSHEET];
    addData(FIRSTROW,FIRTSCOLUMN,"yourdata");


 public void addData(int row, int col, string data)
    {
        worksheet.Cells[row, col] = data;

    }
+3

More articles:


All Articles