【C#进阶】C#实现Excel文件读取的工具类封装

admin · 发表于 2018-4-14 21:08:39

Excel文件读取类：

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
namespace ReadInfoFromDEM
{
class ExcelFileReader
{
private string m_strFileName = null;
private Application m_application = null;
private Workbook m_workBook = null;
private int m_totalSheets = 0;
private int m_activeSheet = 0;
private int m_sheetColumns = 0;
private int m_sheetRows = 0;
public ExcelFileReader(string fileName)
{
m_strFileName = fileName;
}
public string FileName
{
get { return this.m_strFileName; }
set { this.m_strFileName = value; }
}
public int TotalSheets
{
get { return this.m_totalSheets; }
}
public int ActiveSheetIndex
{
get { return this.m_activeSheet; }
}
public int SheetColumns
{
get { return this.m_sheetColumns; }
}
public int SheetRows
{
get { return this.m_sheetRows; }
}
public void ActiveExcelApplication()
{
this.m_application = new Application();
try
{
this.m_workBook = m_application.Workbooks.Open(this.m_strFileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
this.m_totalSheets = this.m_workBook.Sheets.Count;
}
catch
{
}
}
public void ScanExcelSheetSizeByIndex(int sheetIndex)
{
Worksheet sheet = (Worksheet)m_workBook.Sheets[sheetIndex];
Range excelRange = sheet.UsedRange;
this.m_sheetColumns = excelRange.Columns.Count;
this.m_sheetRows = excelRange.Rows.Count;
this.m_activeSheet = sheetIndex;
}
public void ScanExcelSheetSizeByName(string strName)
{
for (int sheetNumber = 1; sheetNumber < m_totalSheets + 1; sheetNumber++)
{
Worksheet sheet = (Worksheet)m_workBook.Sheets[sheetNumber];
if (sheet.Name.ToLower().Equals(strName.ToLower()))
{
this.m_activeSheet = sheetNumber;
Range excelRange = sheet.UsedRange;
this.m_sheetColumns = excelRange.Columns.Count;
this.m_sheetRows = excelRange.Rows.Count;
}
}
}
public String GetCellValue(int row, int column)
{
Worksheet sheet = (Worksheet)m_workBook.Sheets[this.m_activeSheet];
// double类型转换为String 类型
String cellInfo = ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, column]).Value + "";
return cellInfo;
}
public void DeactiveExcelApplication()
{
try
{
this.m_workBook.Close(false, this.m_strFileName, null);
Marshal.ReleaseComObject(this.m_workBook);
}
catch
{
}
}
}
}

复制代码

Excel文件写入类：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace ReadInfoFromDEM
{
class ExcelFileWriter
{
public static Boolean SavetoExcel(DataTable data, string outputPath, bool isColumnWritten, string sheetName = "统计结果")
{
//sheetName = "统计结果";
IWorkbook workbook = null;
FileStream fs = null;
try
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
fs = new FileStream(outputPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (outputPath.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (outputPath.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
if (workbook != null)
{
sheet = workbook.GetSheet(sheetName);//获取工作表
}
else
{
//callback.PrintErrorMessage("\n新建工作表失败！");
return false;
}
//创建列名
if (sheet == null)//如果表单为空，则为datatable初始化列名
{
workbook.CreateSheet(sheetName);
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
//callback.PrintErrorMessage("\n创建Excel表单" + foldername + "出错！");
MessageBox.Show("\n创建Excel表单" + sheetName + "出错！", "提示");
return false;
}
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
workbook.Write(fs); //写入到excel
workbook = null;
fs.Close();
return true;
}
catch (Exception ex)
{
//callback.PrintErrorMessage("\n" + ex.ToString());
return false;
}
return true;
}
//添加新的sheet
public static Boolean SavetoExcelAddNewSheet(DataTable data, string outputPath, bool isColumnWritten, string sheetName = "统计结果")
{
//sheetName = "统计结果";
IWorkbook workbook = null;
FileStream fs = null;
try
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
////创建文件
if (!File.Exists(outputPath))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//保存
FileStream file = new FileStream(outputPath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
fs = new FileStream(outputPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (outputPath.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (outputPath.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (workbook != null)
{
sheet = workbook.GetSheet(sheetName);//获取工作表
if (sheet != null)
{
//callback.PrintErrorMessage("\n已存在工作表" + sheetName + "，请检查统计文件！");
return false;
}
}
else
{
//callback.PrintErrorMessage("\n新建工作表失败！");
return false;
}
//创建列名
if (sheet == null)//如果表单为空，则为datatable初始化列名
{
workbook.CreateSheet(sheetName);
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
//callback.PrintErrorMessage("\n创建Excel表单" + foldername + "出错！");
MessageBox.Show("\n创建Excel表单" + sheetName + "出错！", "提示");
return false;
}
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
//workbook.Write(fs); //写入到excel
//workbook = null;
//fs.Close();
FileStream fout = new FileStream(outputPath, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);//写入流
fout.Flush();
workbook.Write(fout);//写入文件
workbook = null;
fout.Close();
return true;
}
catch (Exception ex)
{
//callback.PrintErrorMessage("\n" + ex.ToString());
return false;
}
return true;
}
}
}

复制代码

附加写入数据类：

private void writeDataToExcel()
{
// 开始写入数据到Excel中去
int row = dataGridView.Rows.Count;
int col = dataGridView.ColumnCount;
// 定义一个二维数组9 * 9
wArray = new string[row, col];
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
// 先读取行数据，再读取列数据
wArray[i, j] = dataGridView.Rows[i].Cells[j].Value.ToString();
}
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
/*for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}*/
//写入数值
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
// 注意worksheet是从1开始的
worksheet.Cells[i + 1, j + 1] = wArray[i, j];
}
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
try
{
workbook.Saved = true;
workbook.SaveCopyAs(inputFilePath); //fileSaved = true;
MessageBox.Show("数据已经成功被写入到Excel文件，保存路径为："+inputFilePath);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
xlApp.Quit();
// 强行销毁
GC.Collect();
}

复制代码

		自动登录	找回密码
密码			立即注册

[技术分享] 【C#进阶】C#实现Excel文件读取的工具类封装

相关帖子

最佳新人

活跃会员

热心会员

推广达人

宣传达人

灌水之王

突出贡献

优秀版主

荣誉管理

论坛元老