Read / Write Excel examples
(it is necessary to change the file name in the code)
=== VB.NET ===
Imports System.Runtime.InteropServices
Imports System.Globalization
Imports System.Threading
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel
'' References -> COM -> Microsoft.Excel XX.0 Object Library
'' Create form and drop Button1 and ListView1 on form
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'' This line is very important!
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US") '<-- change culture on
whatever you need
Dim miss As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer
'' Open Excel
Dim m_objExcel As Excel.Application = New Excel.Application
m_objExcel.Visible = True
Dim m_objBooks As Workbooks = m_objExcel.Workbooks
Try
'' Open workbook
m_objBooks.Open("C:\Points.xls", False, False, miss, "", False, miss, Excel.XlPlatform.xlWindows, miss, True,
miss, miss, miss, False)
Dim m_objBook As Workbook = m_objBooks.Item(1)
m_objBook.Activate() 'optional
Dim m_objSheets As Sheets = m_objBook.Sheets
Dim m_objSheet As Worksheet = m_objSheets.Item(2) '<--"Sheet2"
m_objSheet.Cells.ClearContents() 'optional
Dim m_objCells As Range = m_objSheet.Cells
'' write data starting from cell "A1"
Dim m_objRange As Range = Nothing
For i = 1 To 101
For j = 1 To 3
m_objRange = m_objSheet.Range(m_objCells(i, j), m_objCells(i, j))
m_objRange.Value = Rnd(5).ToString
Next
Next
'' fill the listview with data
ListView1.Clear()
Dim columns() As String = New String() {"X coordinate", "Y coordinate", "Z coordinate"}
For Each column As String In columns
ListView1.Columns.Add(column, 96, HorizontalAlignment.Left)
Next
ListView1.View = View.Details
ListView1.GridLines = True
ListView1.FullRowSelect = True
For row As Integer = 1 To i - 1
Dim lvi As New ListViewItem(m_objSheet.Range(m_objCells(row, 1), m_objCells(row, 1)).Value.ToString, 0)
For col As Integer = 2 To j - 1
lvi.SubItems.Add(m_objSheet.Range(m_objCells(row, col), m_objCells(row, col)).Value.ToString)
Next
Me.ListView1.Items.Add(lvi)
Next
'' Save the file in the typical workbook format
m_objBook.SaveAs("C:\Points.xls", Excel.XlFileFormat.xlWorkbookNormal, "", "", False, False,
XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss)
'' close workbook and quit Excel
m_objBook.Close(False, miss, miss)
m_objExcel.Quit()
Catch ex As System.Exception
MessageBox.Show(ex.StackTrace)
Finally
'' release process if it's still active
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(m_objExcel)
End Try
End Sub
End Class
=== C# ===
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Globalization;
using System.Threading;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
//' References -> COM -> Microsoft.Excel XX.0 Object Library
//' Create form and drop Button1 and ListView1 on it
namespace ExcelForumCS
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Button1_Click(object sender, EventArgs e)
{
//' This line is very important!
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US"); //<-- change culture on
whatever you need
object miss = System.Reflection.Missing.Value;
int i = 0;
int j = 0;
//' Open Excel
Excel.Application m_objExcel = new Excel.Application();
m_objExcel.Visible = true;
Workbooks m_objBooks = m_objExcel.Workbooks;
try
{
//' Open workbook
m_objBooks.Open("C:\\Points.xls", false, false, miss, "", miss, miss, miss, miss, true, miss, miss, miss, miss,
miss);
Workbook m_objBook = m_objBooks.get_Item(1);
//m_objBook.Activate();//optional
Sheets m_objSheets = m_objBook.Sheets;
Worksheet m_objSheet = (Worksheet)m_objSheets.get_Item(1); //<--"Sheet1"
m_objSheet.Cells.ClearContents();
//optional
Range m_objCells = m_objSheet.Cells;
//' write data starting from cell "A1"
Range m_objRange = null;
Random rand = new Random();
for (i = 1; i <= 25; i++)//25 - number of rows
{
for (j = 1; j <= 3; j++)//3 - number of point ordinates
{
m_objRange = m_objSheet.get_Range(m_objCells[i, j], m_objCells[i, j]);
m_objRange.set_Value(miss, (i * 0.12345 * rand.Next(1,10)).ToString());//<--set dummy value
}
}
//' fill the listview with data
ListView1.Clear();
string[] columns = new string[] { "X coordinate", "Y coordinate", "Z coordinate" };
foreach (string column in columns)
{
ListView1.Columns.Add(column, 96, HorizontalAlignment.Left);
}
ListView1.View = View.Details;
ListView1.GridLines = true;
ListView1.FullRowSelect = true;
for (int row = 1; row <= i - 1; row++)
{
ListViewItem lvi = new ListViewItem(m_objSheet.get_Range(m_objCells[row, 1], m_objCells[row,
1]).Value2.ToString(), 0);
for (int col = 2; col <= j - 1; col++)
{
lvi.SubItems.Add(m_objSheet.get_Range(m_objCells[row, 1], m_objCells[row, 1]).Value2.ToString());
}
this.ListView1.Items.Add(lvi);
}
//' Save the file in the typical workbook format
m_objBook.SaveAs("C:\\Points.xls", Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false,
XlSaveAsAccessMode.xlNoChange, miss, miss, miss,
miss, miss);
//' close workbook and quit Excel
m_objBook.Close(false, miss, miss);
m_objExcel.Quit();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
finally
{
//' release process if it's still active
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(m_objExcel);
}
}
}
}
~'J'~