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();
- }
复制代码
|