分享一个比较好用的ExcelHelper类

qq576733600 · 发表于 2013-8-26 10:29:29

提示：
1.需要导入 com 里面的 Microsoft Office 12.0 Object Library 或者 Microsoft Office 11.0 Object Library
2.需要导入 .net 里面的 Microsoft.Office.Interop.Excel
直接看代码：代码太长了，放不下，详细看附件

ExcelHelper.rar (4.48 KB, 下载次数: 821)

using System;
using System.IO;
using System.Data;
using System.Collections;
using System.Data.OleDb;
using System.Web;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
class ExcelHelper
{
string myFileName;
Application myExcel;
Workbook myWorkBook;
/// <summary>
/// 构造函数，不创建Excel工作薄
/// </summary>
public ExcelHelper()
{
}
/// <summary>
/// 创建Excel工作薄
/// </summary>
public void CreateExcel()
{
myExcel = new Application();
myWorkBook = myExcel.Application.Workbooks.Add(true);
}
/// <summary>
/// 显示Excel
/// </summary>
public void ShowExcel()
{
myExcel.Visible = true;
}
/// <summary>
/// 将数据写入Excel
/// </summary>
/// <param name="data">要写入的二维数组数据</param>
/// <param name="startRow">Excel中的起始行</param>
/// <param name="startColumn">Excel中的起始列</param>
public void WriteData(string[,] data, int startRow, int startColumn)
{
int rowNumber = data.GetLength(0);
int columnNumber = data.GetLength(1);
for (int i = 0; i < rowNumber; i++)
{
for (int j = 0; j < columnNumber; j++)
{
//在Excel中，如果某单元格以单引号“'”开头，表示该单元格为纯文本，因此，我们在每个单元格前面加单引号。
myExcel.Cells[startRow + i, startColumn + j] = "'" + data[i, j];
}
}
}
/// <summary>
/// 将数据写入Excel
/// </summary>
/// <param name="data">要写入的字符串</param>
/// <param name="starRow">写入的行</param>
/// <param name="startColumn">写入的列</param>
public void WriteData(string data, int row, int column)
{
myExcel.Cells[row, column] = data;
}
/// <summary>
/// 将数据写入Excel
/// </summary>
/// <param name="data">要写入的数据表</param>
/// <param name="startRow">Excel中的起始行</param>
/// <param name="startColumn">Excel中的起始列</param>
public void WriteData(System.Data.DataTable data, int startRow, int startColumn)
{
for (int i = 0; i <= data.Rows.Count - 1; i++)
{
for (int j = 0; j <= data.Columns.Count - 1; j++)
{
//在Excel中，如果某单元格以单引号“'”开头，表示该单元格为纯文本，因此，我们在每个单元格前面加单引号。
myExcel.Cells[startRow + i, startColumn + j] = "'" + data.Rows[i][j].ToString();
}
}
}
/// <summary>
/// 读取指定单元格数据
/// </summary>
/// <param name="row">行序号</param>
/// <param name="column">列序号</param>
/// <returns>该格的数据</returns>
public string ReadData(int row, int column)
{
Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]);
return range.Text.ToString();
}
/// <summary>
/// 向Excel中插入图片
/// </summary>
/// <param name="pictureName">图片的绝对路径加文件名</param>
public void InsertPictures(string pictureName)
{
Worksheet worksheet = (Worksheet)myExcel.ActiveSheet;
//后面的数字表示位置，位置默认
worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
}
/// <summary>
/// 向Excel中插入图片
/// </summary>
/// <param name="pictureName">图片的绝对路径加文件名</param>
/// <param name="left">左边距</param>
/// <param name="top">右边距</param>
/// <param name="width">宽</param>
/// <param name="heigth">高</param>
public void InsertPictures(string pictureName, int left, int top, int width, int heigth)
{
Worksheet worksheet = (Worksheet)myExcel.ActiveSheet;
worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, top, left, heigth, width);
}
/// <summary>
/// 向Excel中插入图片
/// </summary>
/// <param name="pictureName">图片的绝对路径加文件名</param>
/// <param name="left">左边距</param>
/// <param name="top">右边距</param>
/// <param name="width">宽</param>
/// <param name="heigth">高</param>
/// <param name="cell1">位置</param>
public void InsertPictures(string pictureName, int left, int top, int width, int heigth,object cell1) {
Worksheet worksheet = (Worksheet)myExcel.ActiveSheet;
Range image1 = worksheet.get_Range(cell1, Missing.Value);
image1.Select();
worksheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, top, left, heigth, width);
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="sheetNum">工作表序号，从左到右，从1开始</param>
/// <param name="newSheetName">新的工作表名</param>
public void ReNameSheet(int sheetNum, string newSheetName)
{
Worksheet worksheet = (Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Name = newSheetName;
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="oldSheetName">原有工作表名</param>
/// <param name="newSheetName">新的工作表名</param>
public void ReNameSheet(string oldSheetName, string newSheetName)
{
Worksheet worksheet = (Worksheet)myExcel.Worksheets[oldSheetName];
worksheet.Name = newSheetName;
}
/// <summary>
/// 新建工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
public void CreateWorkSheet(string sheetName)
{
Worksheet newWorksheet = (Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWorksheet.Name = sheetName;
}
/// <summary>
/// 激活工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
public void ActivateSheet(string sheetName)
{
Worksheet worksheet = (Worksheet)myExcel.Worksheets[sheetName];
worksheet.Activate();
}
/// <summary>
/// 激活工作表
/// </summary>
/// <param name="sheetNum">工作表序号</param>
public void ActivateSheet(int sheetNum)
{
Worksheet worksheet = (Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Activate();
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="SheetName">删除的工作表名</param>
public void DeleteSheet(int sheetNum)
{
((Worksheet)myWorkBook.Worksheets[sheetNum]).Delete();
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="SheetName">删除的工作表序号</param>
public void DeleteSheet(string sheetName)
{
((Worksheet)myWorkBook.Worksheets[sheetName]).Delete();
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
public void CellsUnite(int startRow, int startColumn, int endRow, int endColumn)
{
Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.MergeCells = true;
}
/// <summary>
/// 单元格文字对齐方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="hAlign">水平对齐</param>
/// <param name="vAlign">垂直对齐</param>
public void CellsAlignment(int startRow, int startColumn, int endRow, int endColumn, ExcelHAlign hAlign, ExcelVAlign vAlign)
{
Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
range.HorizontalAlignment = hAlign;
range.VerticalAlignment = vAlign;
}
/// <summary>
/// 绘制指定单元格的边框
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn)
{
CellsDrawFrame(startRow, startColumn, endRow, endColumn,
true, true, true, true, true, true, false, false,
LineStyle.连续直线, BorderWeight.细, ColorIndex.自动);
}

复制代码

lcylcyll · 发表于 2013-9-3 12:48:45

能不能载一图片来看一下效果呢！
看一下是不是我想要的呢

Tayirjan · 发表于 2015-12-17 20:00:42

强烈支持楼主ing……

wtf3505 · 发表于 2016-11-19 13:32:54

强烈支持楼主ing……

		自动登录	找回密码
密码			马上注册

[C#语言基础] 分享一个比较好用的ExcelHelper类