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;
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;
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;
// double类型转换为String 类型
String cellInfo = ((Microsoft.Office.Interop.Excel.Range)sheet.Cells).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.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.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.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.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;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
// 先读取行数据， 再读取列数据
wArray = dataGridView.Rows.Cells.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;//取得sheet1
//写入标题
/*for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells = dataGridView1.Columns.HeaderText;
}*/
//写入数值
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
// 注意worksheet是从1开始的
worksheet.Cells = wArray;
}
}
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();
}
评论
填写昵称与邮箱即可评论，无需登录。