C#操作Excel,如果格式是xlsx的就用openXML来操作,如果是xls的就用NPOI来操作
应该讲第三方组件,单独存在一个lib文件夹中,然后添加引用,这样组件就能随着项目走
xlsx实际上是一个压缩文件
using System;
using System.Windows.Forms;
using System.IO;
using NPOI.HSSF.UserModel;
namespace NPOI测试
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 读取EXCEL
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
using (FileStream stream = new FileStream(@"F:\username.xls", FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
MessageBox.Show(workbook.GetSheetName(0),"获取的Sheet名"); //获取sheet名
HSSFSheet sheet= workbook.GetSheetAt(0); //获得第一页的sheet表
HSSFRow row=sheet.GetRow(0); //获得第一行
HSSFCell cell=row.GetCell(0); //获得第一列
string s=cell.ToString(); //输出显示[0][0]单元的内容
MessageBox.Show(s,"获取Excel中[0][0]单元的内容");
//MessageBox.Show(cell.StringCellValue);//输出显示字符串单元格的内容
MessageBox.Show(sheet.LastRowNum.ToString(),"Excel中最后一行的行号");//获取最后总共有多少行
MessageBox.Show(row.LastCellNum.ToString(), "Excel中最后一列的行号");//获取当前行总共有多少列
}
}
/// <summary>
/// 写入Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet();
HSSFRow row = sheet.CreateRow(0);//创建第一行
row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue("Hello");//在第一行第一格写上“Hello”
row.CreateCell(1, HSSFCell.CELL_TYPE_NUMERIC).SetCellValue(3.14);//写上浮点型的数字
using (FileStream stream = new FileStream(@"f:\1.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(stream);
}
MessageBox.Show("创建成功");
}
}
}
转载于:https://blog.51cto.com/dingxiaowei/1366617