This is what i am trying to do.
1.Create excel file(.xlsx) c://test/files/work1_4.13.14.xlsx with name + value(date)
example: work1_4.13.14.xlsx
2.Set headers to the file example: [Name] [Age] [City].
3.I have 3 List with names,ages,city that i need to fill into excel sheet.
This is my goal
Name Age City
Ben 20 xyz
Jack 25 xyz
Mike 45 zyx
Any ideas?
just follow below steps:
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
You can use ClosedXML for this.
Store your table in a DataTable and you can export the table to excel by this simple snippet:
XLWorkbook workbook = new XLWorkbook();
DataTable table = GetYourTable();
workbook.Worksheets.Add(table );
You can read the documentation of ClosedXML to learn more. Hope this helps!
It is possible to write to an excel file without opening it using the Microsoft.Jet.OLEDB.4.0
and OleDb
. Using OleDb
, it behaves as if you were writing to a table using sql.
Here is the code I used to create and write to an new excel file. No extra references are needed
var connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SomePath\ExcelWorkBook.xls;Extended Properties=Excel 8.0";
using (var excelConnection = new OleDbConnection(connectionString))
{
// The excel file does not need to exist, opening the connection will create the
// excel file for you
if (excelConnection.State != ConnectionState.Open) { excelConnection.Open(); }
// data is an object so it works with DBNull.Value
object propertyOneValue = "cool!";
object propertyTwoValue = "testing";
var sqlText = "CREATE TABLE YourTableNameHere ([PropertyOne] VARCHAR(100), [PropertyTwo] INT)";
// Executing this command will create the worksheet inside of the workbook
// the table name will be the new worksheet name
using (var command = new OleDbCommand(sqlText, excelConnection)) { command.ExecuteNonQuery(); }
// Add (insert) data to the worksheet
var commandText = $"Insert Into YourTableNameHere ([PropertyOne], [PropertyTwo]) Values (@PropertyOne, @PropertyTwo)";
using (var command = new OleDbCommand(commandText, excelConnection))
{
// We need to allow for nulls just like we would with
// sql, if your data is null a DBNull.Value should be used
// instead of null
command.Parameters.AddWithValue("@PropertyOne", propertyOneValue ?? DBNull.Value);
command.Parameters.AddWithValue("@PropertyTwo", propertyTwoValue ?? DBNull.Value);
command.ExecuteNonQuery();
}
}
Hope here is the exact what we are looking for.
private void button2_Click(object sender, RoutedEventArgs e)
{
UpdateExcel("Sheet3", 4, 7, "Namachi@gmail");
}
private void UpdateExcel(string sheetName, int row, int col, string data)
{
Microsoft.Office.Interop.Excel.Application oXL = null;
Microsoft.Office.Interop.Excel._Workbook oWB = null;
Microsoft.Office.Interop.Excel._Worksheet oSheet = null;
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oWB = oXL.Workbooks.Open("d:\\MyExcel.xlsx");
oSheet = String.IsNullOrEmpty(sheetName) ? (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet : (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets[sheetName];
oSheet.Cells[row, col] = data;
oWB.Save();
MessageBox.Show("Done!");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (oWB != null)
oWB.Close();
}
}
Source: Stackoverflow.com