using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient;namespace 导入到Excel { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("server=.;database=pubs;uid=sa;pwd="); conn.Open(); string strsql = "select * from jobs"; SqlDataAdapter da = new SqlDataAdapter(strsql, conn); DataSet ds = new DataSet();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
//gaoyang [10/21/2006] 经测试如果设置为 GetEncoding("GB2312"),导出的文件将会出现乱码。
Response.ContentEncoding = System.Text.Encoding.UTF8; //设置输出文件类型为excel文件。
Response.ContentType = "application/ms-excel";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.grvAutoCourse.RenderControl(oHtmlTextWriter);/////
Response.Output.Write(oStringWriter.ToString());
Response.Flush();
Response.End();
/// <summary>
/// 在EXCEL导出的时候必须重写该方法
/// </summary>
/// <param name="control"></param>
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Reflection;
using System.IO;
namespace ExcelTest
{
class Program
{
static void Main(string[] args)
{
////创建Application对象
Excel.Application xlsApp = new Excel.Application();
if (xlsApp == null)
{
return;
} xlsApp.Visible = true;
//得到WorkBook对象, 可以用两种方式
//之一: 打开已有的文件
//Excel.Workbook xlsBook = xlsApp.Workbooks.Open(@"E:\Documents and Settings\daniel.chen\Desktop\test.xls",Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//之二：新建一个文件
Excel.Workbook xlsBook = xlsApp.Workbooks.Add(Missing.Value);
//指定要操作的Sheet，两种方式
//之一：
Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsBook.Sheets[1];
//之二：
//Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsApp.ActiveSheet;
//指定单元格，读取数据，两种方法
//之一：
Excel.Range range1 = xlsSheet.get_Range("C2",Type.Missing);
Console.WriteLine(range1.Value2);
//之二：
Excel.Range range2 = (Excel.Range)xlsSheet.Cells[2, 3];
Console.WriteLine(range2.Value2);
//在单元格中写入数据
Excel.Range range3 = xlsSheet.get_Range("A1",Type.Missing);
range3.Value2 = "Hello World!";
range3.Borders.Color = Color.FromArgb(123, 231, 32).ToArgb();
range3.Font.Color = Color.Red.ToArgb();
range3.Font.Name = "Arial";
range3.Font.Size = 9;
//range3.Orientation = 90; //vertical
range3.Columns.HorizontalAlignment = Excel.Constants.xlCenter;
range3.VerticalAlignment = Excel.Constants.xlCenter;
range3.Interior.Color = Color.FromArgb(192,192,192).ToArgb();
range3.Columns.AutoFit();//adjust the column width automatically
//在某个区域写入数据数组
int matrixHeight = 20;
int matrixWidth = 20;
string[,] martix=new string[matrixHeight,matrixWidth];
for (int i = 0; i < matrixHeight; i++)
for (int j = 0; j < matrixWidth; j++)
{
martix[i, j] = String.Format("{0}_{1}", i+1, j+1);
}
string startColName=GetColumnNameByIndex(0);
string endColName=GetColumnNameByIndex(matrixWidth-1);
//取得某个区域，两种方法
//之一：
Excel.Range range4 = xlsSheet.get_Range("A1",Type.Missing);
range4 = range4.get_Resize(matrixHeight,matrixWidth);
//之二：
//Excel.Range range4 = xlsSheet.get_Range(String.Format("{0}{1}", startColName, 1), String.Format("{0}{1}", endColName, martixHeight));
range4.Value2 = martix;
range4.Font.Color = Color.Red.ToArgb();
range4.Font.Name="Arial";
range4.Font.Size = 9;
range4.Columns.HorizontalAlignment = Excel.Constants.xlCenter;
//设置column和row的宽度和颜色
int columnIndex=3;
int rowIndex=3;
string colName = GetColumnNameByIndex(columnIndex);
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.ColumnWidth = 20;
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Rows.RowHeight = 40;
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.Interior.Color = Color.Blue.ToArgb();//单格颜色
xlsSheet.get_Range(5 + ":" + 7, Type.Missing).Rows.Interior.Color = Color.Yellow.ToArgb();//第5行到第7行的颜色
//xlsSheet.get_Range("G : G", Type.Missing).Columns.Interior.Color=Color.Pink.ToArgb();//第n列的颜色如何设置？？ //保存，关闭
if (File.Exists(@"E:\Documents and Settings\daniel.chen\Desktop\test1.xls"))
{
File.Delete(@"E:\Documents and Settings\daniel.chen\Desktop\test1.xls");
}
xlsBook.SaveAs(@"E:\Documents and Settings\daniel.chen\Desktop\test1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlsBook.Close(false, Type.Missing, Type.Missing);
xlsApp.Quit(); GC.Collect(); Console.ReadKey();
} //将column index转化为字母，至多两位
public static string GetColumnNameByIndex(int index)
{
string[] alphabet = new string[] {"","A", "B", "C", "D", "E", "F", "G", "H", "I", "J" ,"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
string result = "";
int temp = index / 26;
int temp2 = index % 26 + 1;
if (temp > 0)
{
result += alphabet[temp];
}
result += alphabet[temp2];
return result;
}
}
}C#读写EXCEL方法二
通过数据库方式 OleDbDataAdapter ada = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=Excel 8.0;");
DataTable dt = new DataTable();
try
{
ada.Fill(dt);
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
dataGrid1.DataSource = dt; http://www.cnblogs.com/Roger52027/archive/2006/12/28/606016.aspx
http://hi.baidu.com/bluelight4587/blog/item/72020df3451fc3c80a46e081.html
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + file + ";" +
"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet ExcelDs = new DataSet();
try
{
ExcelDA.Fill(ExcelDs, "ExcelInfo"); DataTable dt = ExcelDs.Tables[0]; return dt; }
catch (Exception err)
{
Response.Write(err.ToString());
} return null;
写文件：
以"\t" 和"\r\n"分隔文本输出，其中"\t"是列分隔符，"\r\n"是行分隔符，然后文件格式定义为XLS就可以，但如果想输出带格式设置的XLS文件，则需要使用EXCEL控件
OleDbDataAdapter myDa;
string ExcelConnectString = "Provider = Microsoft.Jet.OLEDB.4.0 ;
Data Source = '" + filepath + "';Extended Properties='Excel
8.0;HDR=YES;IMEX=1'";
try
{
cnnxls = new OleDbConnection(ExcelConnectString);
myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
myDa.Fill(ds);
myDa.Dispose();
cnnxls.Close();
}
catch (Exception)
{
}
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;namespace 导入到Excel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void Form1_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=pubs;uid=sa;pwd=");
conn.Open();
string strsql = "select * from jobs";
SqlDataAdapter da = new SqlDataAdapter(strsql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
this.dataGridView1.DataSource=ds.Tables[0];
} private void button1_Click(object sender, EventArgs e)
{
print(dataGridView1);
}
public void print(DataGridView dataGridView1)
{
try
{
if (dataGridView1.Rows.Count == 0)
{
return;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.Application.Workbooks.Add(true);
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
for(int i=0;i<dataGridView1.Rows.Count-1;i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] =dataGridView1[j, i].Value.ToString();
}
}
}
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.Application.Workbooks.Add(true).Save();
excel.Save("Test.xls");
excel.Quit();
excel = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ExcelName;Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
//String sql = "SELECT * FROM [sheet1$]"; OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, excelName);
OleConn.Close();
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Configuration;
using System.Collections;
using Excel;namespace ProtoType
{
///
/// 套用模板输出Excel，生成xls文件和html文件
/// Author: Liu Wen
/// Date Created: 2006-8
///
public class ExportExcel
{
#region variable member
protected string templateFile = null;
protected string excelFile = null;
protected string htmlFile = null;
protected object missing = Missing.Value;
Excel.ApplicationClass app;
Excel.Workbook book;
Excel.Worksheet sheet;
Excel.Range range;
private DateTime beforeTime; //Excel启动之前时间
private DateTime afterTime; //Excel启动之后时间
#endregion ///
/// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径
///
/// Excel模板文件路径
/// Excel输出文件路径
/// Html输出文件路径
public ExportExcel(string templateFile, string excelFile, string htmlFile)
{
if(templateFile == null)
throw new Exception("Excel模板文件路径不能为空！"); if(excelFile == null)
throw new Exception("Excel输出文件路径不能为空！"); if(htmlFile == null)
throw new Exception("Html输出文件路径不能为空！"); if(!File.Exists(templateFile))
throw new Exception("指定路径的Excel模板文件不存在！"); this.templateFile = templateFile;
this.excelFile = excelFile;
this.htmlFile = htmlFile; //创建一个Application对象
beforeTime = DateTime.Now;
app = new ApplicationClass();
//app.Visible = true;
afterTime = DateTime.Now; //打开模板文件，得到WorkBook对象
try
{
book = app.Workbooks.Open(templateFile,missing,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing,missing,missing,missing);
}
catch (Exception e)
{
throw e;
}
//得到WorkSheet对象
sheet = (Excel.Worksheet)book.Sheets.get_Item(1);
} ///
/// 将DataTable数据导出到Excel（可动态插入行）
///
/// DataTable
/// 插入行的索引
/// 插入列的索引
public void DataTableToExcel(System.Data.DataTable dt, int rowIndex, int colIndex)
{
//range = sheet.get_Range("A7", missing);
//range.Value2 = "raogerrr";
//string str = range.Text.ToString(); int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
int iRow;
int iCol; //将数据导出到相应的单元格
for (iRow = 0; iRow < rowCount; iRow++)
{
//插入新行
this.InsertRows(sheet, iRow+rowIndex);
//填充当前行
for (iCol = 0; iCol < colCount; iCol++)
{
sheet.Cells[iRow+rowIndex, iCol+colIndex] = dt.Rows[iRow][iCol].ToString();
}
}
this.DeleteRows(sheet, rowCount+rowIndex); //Excel.QueryTables qts = sheet.QueryTables;
//Excel.QueryTable qt = qts.Add(,,);
//qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
//qt.Refresh();
} ///
/// 将DataTable数据导出到Excel（可动态插入行）
///
/// DataTable
/// 插入数据的起始单元格
public void DataTableToExcel(System.Data.DataTable dt, string cellID)
{
int rowIndex = sheet.get_Range(cellID, missing).Row;
int colIndex = sheet.get_Range(cellID, missing).Column;
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
int iRow;
int iCol; //利用二维数组批量写入
string[,] array = new string[rowCount,colCount];
for (iRow = 0; iRow < rowCount; iRow++)
{
for (iCol = 0; iCol < colCount; iCol++)
{
array[iRow,iCol] = dt.Rows[iRow][iCol].ToString();
}
} for (iRow = 0; iRow < rowCount; iRow++)
{
this.InsertRows(sheet, iRow+rowIndex);
}
this.DeleteRows(sheet, rowCount+rowIndex); range = sheet.get_Range(cellID, missing);
range = range.get_Resize(rowCount, colCount);
range.Value2 = array;
} ///
/// 将DataTable数据导出到Excel（固定）
///
/// DataTable
/// 插入数据的起始单元格
public void DataTableToExcel2(System.Data.DataTable dt, string cellID)
{
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
int iRow;
int iCol; //利用二维数组批量写入
string[,] array = new string[rowCount,colCount];
for (iRow = 0; iRow < rowCount; iRow++)
{
for (iCol = 0; iCol < colCount; iCol++)
{
array[iRow,iCol] = dt.Rows[iRow][iCol].ToString();
}
} range = sheet.get_Range(cellID, missing);
range = range.get_Resize(rowCount, colCount);
range.Value2 = array;
} ///
/// 输出生成的Excel, Html文件
///
public void OutputFile()
{
if (this.excelFile == null)
throw new Exception("没有指定Excel输出文件路径！");
if (this.htmlFile == null)
throw new Exception("没有指定Htmll输出文件路径！");
try
{
book.SaveAs(excelFile, missing, missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing,missing); book.SaveAs(htmlFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
} ///
/// 在工作表中插入行，并调整其他行以留出空间
///
/// 当前工作表
/// 欲插入的行索引
private void InsertRows(Excel.Worksheet sheet, int rowIndex)
{
Range r = (Excel.Range)sheet.Rows[rowIndex, missing]; //object Range.Insert(object shift, object copyorigin);
//shift: Variant类型，可选。指定单元格的调整方式。可以为下列 XlInsertShiftDirection 常量之一：
//xlShiftToRight 或 xlShiftDown。如果省略该参数，Microsoft Excel 将根据区域形状确定调整方式。
r.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
} ///
/// 在工作表中删除行
///
/// 当前工作表
/// 欲删除的行索引
private void DeleteRows(Excel.Worksheet sheet, int rowIndex)
{
Range r = (Range)sheet.Rows[rowIndex, missing]; r.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
} ///
/// 退出Excel，并且释放调用的COM资源
///
private void Dispose()
{
book.Close(missing, missing, missing);
app.Workbooks.Close();
app.Quit(); if(range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if(sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
}
if(book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
book = null;
}
if(app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
} GC.Collect();
this.KillExcelProcess();
} ///
/// 结束Excel进程
///
private void KillExcelProcess()
{
DateTime startTime;
Process[] processes = Process.GetProcessesByName("Excel"); //得不到Excel进程ID，暂时只能判断进程启动时间
foreach (Process process in processes)
{
startTime = process.StartTime;
if(startTime > beforeTime && startTime < afterTime)
process.Kill();
}
} }
}
首先Add References Excel.dll，注意在MS office 2003的版本中，可能找不到Excel.dll,这时需要从Excel.exe中扣出来，怎么扣？网上有很多方法，扣出来的同时也把Excel.dll 变成了受托管的。
1.读取Excel File。
其实读取Excel和读取数据库的方式差不多：#region
1 string StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=NO'";
2 OleDbConnection MyConn = new OleDbConnection(StrConn);
3 MyConn.Open();
4 string StrCmd = "select * from [sheet1$]";
5 OleDbDataAdapter MyCommand = new OleDbDataAdapter(StrCmd,MyConn);
6 DataSet Ds = new DataSet();
7 MyCommand.Fill(Ds,"NameTB");
8 DataTable dt=Ds.Tables["NameTB"]
＃endregion 如果Excel第一行是表头的话，可以把StrConn里面的HDR=NO改成，HDR=YES; 当然这样读出来的数据有时候是有问题的，比如本来只有3行的数据，却读来有>3行，我还不知道是什么原因，哪位知道告诉我谢谢。那么这个时候可以用 下面的方法判断真实的行数：
1 int b = 0;
2 if (Ds.Tables["NameTB"].Rows[Ds.Tables["NameTB"].Rows.Count - 1][0].ToString() != string.Empty)
3 b = Ds.Tables["NameTB"].Rows.Count;
4 else
5 {
6 while (Ds.Tables["NameTB"].Rows[b][0].ToString() != string.Empty)
7 {
8 b++;
9 }
10 }
这里的Excel中的数据是一列多行。同时在从Table 中取出来用的时候要保证Ds.Tables["NameTB"].Rows[i][0].ToString() .Trim()!= string.Empty。 2.生成Excel并插入数据。
有时我们需要把检索出来的数据导出到Excel。下面说说如何生成,插入数据到Excel. 首先Add References Excel.dll，引用using Excel;
下面的代码是将ListView中的数据导入到Excel：
1 System.Reflection.Missing miss = System.Reflection.Missing.Value;
2 Excel.ApplicationClass m_objExcel = new Excel.ApplicationClass();
m_objExcel.Visible = false;
3 Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
4 Excel.Workbook m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
5 Excel.Worksheet m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;
6 Excel.Range ER = null;16 ER = m_objSheet.get_Range((object)"A1", System.Reflection.Missing.Value);
17 ER.Value2 = "Last Name";
18 ER.ColumnWidth = 12;
19 ER = m_objSheet.get_Range((object)"B1", System.Reflection.Missing.Value);
20 ER.Value2 = "Frst Name";
21 ER.ColumnWidth = 15;
22 ER = m_objSheet.get_Range((object)"C1", System.Reflection.Missing.Value);
23 ER.Value2 = "Address";
24 ER.ColumnWidth = 35;43 m_objSheet.get_Range("A1", "D1").Font.Bold = true;
45 m_objSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
47 for (int i = 2; i < this.lvResult.Items.Count + 2; i++)
48 {
50 m_objExcel.Cells[i, 1] = this.lvResult.Items[i - 2].SubItems[1].Text.ToString().Trim();
51 m_objExcel.Cells[i, 2] = this.lvResult.Items[i - 2].SubItems[2].Text.ToString().Trim();
52 m_objExcel.Cells[i, 3] = this.lvResult.Items[i - 2].SubItems[3].Text.ToString().Trim();
59 }
//生成Excel.
61 m_objBook.SaveAs(SavePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
62 //释放资源。
63 m_objBook.Close(false, miss, miss);
64 m_objBooks.Close();
65 m_objExcel.Quit();
66
67 System.Runtime.InteropServices.Marshal.ReleaseComObject(ER);
68 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
69 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
70 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
71 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
72 GC.Collect();
对IO操作一般都要考虑到异常，自己添加。在网上也找了一些例子，觉得上面的资源释放机制是相对比较好的
C#读写EXCEL方法一
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Reflection;
using System.IO;
namespace ExcelTest
{
class Program
{
static void Main(string[] args)
{
////创建Application对象
Excel.Application xlsApp = new Excel.Application();
if (xlsApp == null)
{
return;
} xlsApp.Visible = true;
//得到WorkBook对象, 可以用两种方式
//之一: 打开已有的文件
//Excel.Workbook xlsBook = xlsApp.Workbooks.Open(@"E:\Documents and Settings\daniel.chen\Desktop\test.xls",Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//之二：新建一个文件
Excel.Workbook xlsBook = xlsApp.Workbooks.Add(Missing.Value);
//指定要操作的Sheet，两种方式
//之一：
Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsBook.Sheets[1];
//之二：
//Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsApp.ActiveSheet;
//指定单元格，读取数据，两种方法
//之一：
Excel.Range range1 = xlsSheet.get_Range("C2",Type.Missing);
Console.WriteLine(range1.Value2);
//之二：
Excel.Range range2 = (Excel.Range)xlsSheet.Cells[2, 3];
Console.WriteLine(range2.Value2);
//在单元格中写入数据
Excel.Range range3 = xlsSheet.get_Range("A1",Type.Missing);
range3.Value2 = "Hello World!";
range3.Borders.Color = Color.FromArgb(123, 231, 32).ToArgb();
range3.Font.Color = Color.Red.ToArgb();
range3.Font.Name = "Arial";
range3.Font.Size = 9;
//range3.Orientation = 90; //vertical
range3.Columns.HorizontalAlignment = Excel.Constants.xlCenter;
range3.VerticalAlignment = Excel.Constants.xlCenter;
range3.Interior.Color = Color.FromArgb(192,192,192).ToArgb();
range3.Columns.AutoFit();//adjust the column width automatically
//在某个区域写入数据数组
int matrixHeight = 20;
int matrixWidth = 20;
string[,] martix=new string[matrixHeight,matrixWidth];
for (int i = 0; i < matrixHeight; i++)
for (int j = 0; j < matrixWidth; j++)
{
martix[i, j] = String.Format("{0}_{1}", i+1, j+1);
}
string startColName=GetColumnNameByIndex(0);
string endColName=GetColumnNameByIndex(matrixWidth-1);
//取得某个区域，两种方法
//之一：
Excel.Range range4 = xlsSheet.get_Range("A1",Type.Missing);
range4 = range4.get_Resize(matrixHeight,matrixWidth);
//之二：
//Excel.Range range4 = xlsSheet.get_Range(String.Format("{0}{1}", startColName, 1), String.Format("{0}{1}", endColName, martixHeight));
range4.Value2 = martix;
range4.Font.Color = Color.Red.ToArgb();
range4.Font.Name="Arial";
range4.Font.Size = 9;
range4.Columns.HorizontalAlignment = Excel.Constants.xlCenter;
//设置column和row的宽度和颜色
int columnIndex=3;
int rowIndex=3;
string colName = GetColumnNameByIndex(columnIndex);
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.ColumnWidth = 20;
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Rows.RowHeight = 40;
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.Interior.Color = Color.Blue.ToArgb();//单格颜色
xlsSheet.get_Range(5 + ":" + 7, Type.Missing).Rows.Interior.Color = Color.Yellow.ToArgb();//第5行到第7行的颜色
//xlsSheet.get_Range("G : G", Type.Missing).Columns.Interior.Color=Color.Pink.ToArgb();//第n列的颜色如何设置？？ //保存，关闭
if (File.Exists(@"E:\Documents and Settings\daniel.chen\Desktop\test1.xls"))
{
File.Delete(@"E:\Documents and Settings\daniel.chen\Desktop\test1.xls");
}
xlsBook.SaveAs(@"E:\Documents and Settings\daniel.chen\Desktop\test1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlsBook.Close(false, Type.Missing, Type.Missing);
xlsApp.Quit(); GC.Collect(); Console.ReadKey();
} //将column index转化为字母，至多两位
public static string GetColumnNameByIndex(int index)
{
string[] alphabet = new string[] {"","A", "B", "C", "D", "E", "F", "G", "H", "I", "J" ,"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
string result = "";
int temp = index / 26;
int temp2 = index % 26 + 1;
if (temp > 0)
{
result += alphabet[temp];
}
result += alphabet[temp2];
return result;
}
}
}C#读写EXCEL方法二
通过数据库方式 OleDbDataAdapter ada = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=Excel 8.0;");
DataTable dt = new DataTable();
try
{
ada.Fill(dt);
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
dataGrid1.DataSource = dt;
}