这里是一些整理的Excel的一些简单的操作 ，谁有更加详细的希望可以交流 谢谢
/// <summary>
/// ExcelEdit 的摘要说明
/// </summary>
public class ExcelEdit
{
public string mFilename;
public Microsoft.Office.Interop.Excel.Application app;
public Microsoft.Office.Interop.Excel.Workbooks wbs;
public Microsoft.Office.Interop.Excel.Workbook wb;
public Microsoft.Office.Interop.Excel.Worksheets wss;
public Microsoft.Office.Interop.Excel.Worksheet ws;
public ExcelEdit()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void Create()//创建一个Excel对象
{
try
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 读取Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <returns>返回一个数据集</returns>
public DataTable ExcelToTable(string tablename)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + mFilename+ ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataTable dt = null;
strExcel = "select * from [" + tablename + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
return dt;
}
public void Open(string FileName)//打开一个Excel文件
{
try
{
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true,Type.Missing,Type.Missing);
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
mFilename = FileName;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)//获取一个工作表
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName];
return s;
}
public Microsoft.Office.Interop.Excel.Worksheet GetSheet(int index)//获取一个工作表
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[index];
return s;
}
public Microsoft.Office.Interop.Excel.Worksheet AddSheet(string SheetName)//添加一个工作表
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
s.Name = SheetName;
return s;
}
public void DelSheet(string SheetName)//删除一个工作表
{
((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
}
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[OldSheetName];
s.Name = NewSheetName;
return s;
}
public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二
{
Sheet.Name = NewSheetName;
return Sheet;
}
public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)//ws：要设值的工作表 X行Y列 value 值
{
ws.Cells[x, y] = value;
}
public void SetCellValue(string ws, int x, int y, object value)//ws：要设值的工作表的名称 X行Y列 value 值
{
GetSheet(ws).Cells[x, y] = value;
}
public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)//设置一个单元格的属性 字体， 大小，颜色 ，对齐方式
{
name = "宋体";
size = 12;
color = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
}
public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
{
//name = "宋体";
//size = 12;
//color = Excel.Constants.xlAutomatic;
//HorizontalAlignment = Excel.Constants.xlRight;
Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
}
public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)//合并单元格
{
ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
}
public void UniteCells(string ws, int x1, int y1, int x2, int y2)//合并单元格
{
GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);
}
public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
}
}
}
public void InsertTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置二
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
ws.Cells[startX + i, j + startY] = dt.Rows[i][j];
}
}
}
public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置一
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j];
}
}
}
public void AddTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置二
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
ws.Cells[i + startX, j + startY] = dt.Rows[i][j];
}
}
}
public void InsertActiveChart(Microsoft.Office.Interop.Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Microsoft.Office.Interop.Excel.XlRowCol ChartDataType)//插入图表操作
{
ChartDataType = Microsoft.Office.Interop.Excel.XlRowCol.xlColumns;
wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
{
wb.ActiveChart.ChartType = ChartType;
wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType);
wb.ActiveChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAsObject, ws);
}
}
public bool Save()//保存文档
{
if (mFilename == "")
{
return false;
}
else
{
try
{
wb.Save();
return true;
}
catch (Exception ex)
{
return false;
}
}
}
public bool SaveAs(object FileName)//文档另存为
{
try
{
wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return true;
}
catch (Exception ex)
{
return false;
}
}
public void Close()//关闭一个Excel对象，销毁对象
{
//wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
}
