using System;
using Microsoft.Office.Core;
using Excel;
namespace WebExcell1
{
/// <summary>
/// Class1 的摘要说明。
/// </summary>
class ExcelEdit
{
string myFileName;
Excel.Application myExcel;
Excel.Workbook myWorkBook;
public string MyFileName
{
get
{
return myFileName;
}
set
{
myFileName = value;
}
}
/// <summary>
/// 构造函数,不创建Excel工作薄
/// </summary>
public ExcelEdit()
{
//请不要删除以下信息
//版权:http://XingFuStar.cnblogs.com
}
/// <summary>
/// 创建Excel工作薄
/// </summary>
public void CreateExcel()
{
myExcel = new Excel.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)
{
Excel.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)
{
Excel.Worksheet worksheet = (Excel.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)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.ActiveSheet;
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)
{
Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Name = newSheetName;
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="oldSheetName">原有工作表名</param>
/// <param name="newSheetName">新的工作表名</param>
public void ReNameSheet(string oldSheetName, string newSheetName)
{
Excel.Worksheet worksheet = (Exc