1. 读EXCEL文件
using (FileStream stream = new FileStream(@"c:\test.xls", FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
HSSFSheet sheet = workbook.GetSheetAt(0);
HSSFRow row = sheet.GetRow(0);
string value1 = row.GetCell(0).StringCellValue;
MessageBox.Show(value1);
double value2= row.GetCell(1).NumericCellValue;
MessageBox.Show(value2.ToString());
DateTime value3 = row.GetCell(2).DateCellValue;
MessageBox.Show(value3.ToString());
}
NPOI能够分析Excel文件的格式,能够进行常用Excel操作,不依赖于Excel,节省资源,没有安全性、性能的问题,在ASP.net中用最合适。只能处理xls格式文件、不能处理xlsx这样的新版本Excel文件格式。处理xlsx还要用OpenXML。2.写XLS文件
private void btnWriter_Click(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value;
ApplicationClass app = new ApplicationClass();
app.SheetsInNewWorkbook = 1;
app.Visible = true;
Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet sheet = (Worksheet)wb.Sheets[1];
for (int i = 1; i <= 1000; i++)
{
sheet.get_Range("A" + i, "A" + i).Value2 = i.ToString();
}
wb.SaveAs("c:/1.xls", XlFileFormat.xlExcel9795, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
app.Quit();
}
注:使用时需添加NPOI组件包。
2.写入EXCEL文件
private void button1_Click(object sender, EventArgs e)
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet();
HSSFRow row = sheet.CreateRow(0);
HSSFCell cell1 = row.CreateCell(0,HSSFCell.CELL_TYPE_STRING);
cell1.SetCellValue("hello");
HSSFCell cell2 = row.CreateCell(1, HSSFCell.CELL_TYPE_NUMERIC);
cell2.SetCellValue(3.14);
using (FileStream stream = new FileStream(@"c:\writetest.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
wb.Write(stream);
}
}