今天对从网上搜到的C#操作EXCEL的方法总结了一下，写了一个类，里边代码很简单。不多说。希望砖头不要扔太多。。。。。。。。
public class ExcelBuss
{
/// <summary>
/// 应用程序
/// </summary>
Microsoft.Office.Interop.Excel.Application excel = null;
/// <summary>
/// 工作布
/// </summary>
Microsoft.Office.Interop.Excel.Workbook workbook = null;
/// <summary>
/// 表单
/// </summary>
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
/// <summary>
/// 创建应用程序
/// </summary>
public void CreatExcelApp()
{
excel = new Microsoft.Office.Interop.Excel.Application();
}
/// <summary>
/// 添加工作布
/// </summary>
public void AddWorkBook()
{
workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
}
/// <summary>
/// 获取表单数量
/// </summary>
/// <returns></returns>
public int GetWorkSheetCount()
{
return workbook.Worksheets.Count;
}
/// <summary>
/// 获取第N个表单
/// </summary>
/// <param name="i">第N个表单</param>
/// <returns>表单</returns>
public Worksheet GetWorkSheet(int i)
{
return (Worksheet)workbook.Worksheets.get_Item(i);
}
/// <summary>
/// 设置表单
/// </summary>
/// <param name="sheet">表单</param>
public void SetCurrentWorkSheet(Worksheet sheet)
{
worksheet = sheet;
}
/// <summary>
/// 添加表单
/// </summary>
public void AddWorkSheet()
{
workbook.Worksheets.Add();
}
/// <summary>
/// 设置表单名称
/// </summary>
/// <param name="name"></param>
public void SetWorkSheetName(string name)
{
worksheet.Name = name;
}
/// <summary>
/// 设置行高
/// </summary>
/// <param name="row">行索引</param>
/// <param name="hight">高度</param>
public void SetRowHight(int row, int hight)
{
worksheet.Rows[row].RowHeight = hight;
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="column">列索引</param>
/// <param name="width">宽度</param>
public void SetColumnWidth(int column, int width)
{
worksheet.Columns[column].ColumnWidth = width;
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="row1">第一个单元格的行索引</param>
/// <param name="column1">第一个单元格的列索引</param>
/// <param name="row2">最后一个单元格的行索引</param>
/// <param name="column2">最后一个单元格的列索引</param>
public void MergeCells(int row1, int column1, int row2, int column2)
{
Range r = GetRang(row1, column1, row2, column2);
r.Merge();
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="r1">第一个单元格</param>
/// <param name="r2">最后一个单元格</param>
public void MergeCells(Range r1, Range r2)
{
Range r = worksheet.get_Range(r1, r2);
r.Merge();
}
/// <summary>
/// 获取单元格
/// </summary>
/// <param name="row1">第一个单元格的行索引</param>
/// <param name="column1">第一个单元格的列索引</param>
/// <param name="row2">最后一个单元格的行索引</param>
/// <param name="column2">最后一个单元格的列索引</param>
/// <returns>单元格</returns>
public Range GetRang(int row1, int column1, int row2 = -1, int column2 = -1)
{
if (row2 == -1 && column2 == -1)
{
return worksheet.get_Range((Range)worksheet.Cells[row1, column1]);
}
else
{
return worksheet.get_Range((Range)worksheet.Cells[row1, column1], (Range)worksheet.Cells[row2, column2]);
}
}
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="row">行索引</param>
/// <param name="column">列索引</param>
/// <param name="value">值</param>
public void SetCellValue(int row, int column, string value)
{
worksheet.Cells[row, column].Value = value;
}
/// <summary>
/// 设置单元格的字体格式
/// </summary>
/// <param name="range">单元格</param>
/// <param name="fontName">字体名</param>
/// <param name="fontSize">字体大小</param>
/// <param name="fontColor">字体颜色</param>
public void SetFont(Range range, string fontName, int fontSize, Color fontColor)
{
range.Font.Name = fontName;
range.Font.Size = fontSize;
range.Font.Color = fontColor;
}
/// <summary>
/// 设置单元格的字体格式
/// </summary>
/// <param name="row">行索引</param>
/// <param name="column">列索引</param>
/// <param name="fontName">字体名</param>
/// <param name="fontSize">字体大小</param>
/// <param name="fontColor">字体颜色</param>
public void SetFont(int row, int column, string fontName, int fontSize, Color fontColor)
{
Range range = GetRang(row, column);
range.Font.Name = fontName;
range.Font.Size = fontSize;
range.Font.Color = fontColor;
}
/// <summary>
/// 设置单元格的背景色
/// </summary>
/// <param name="range">单元格</param>
/// <param name="backColor">颜色</param>
public void SetBackColor(Range range, Color backColor)
{
range.Cells.Interior.Color = backColor;
}
/// <summary>
/// 设置单元格的背景色
/// </summary>
/// <param name="row">行索引</param>
/// <param name="column">列索引</param>
/// <param name="backColor">颜色</param>
public void SetBackColor(int row, int column, Color backColor)
{
SetBackColor(GetRang(row, column), backColor);
}
/// <summary>
/// 设置单元格边框
/// </summary>
/// <param name="range">单元格</param>
/// <param name="lineStyle">样式</param>
public void SetLineStyle(Range range, int lineStyle = 1)
{
range.Borders.LineStyle = lineStyle;
}
/// <summary>
/// 设置单元格边框
/// </summary>
/// <param name="row">行索引</param>
/// <param name="column">列索引</param>
/// <param name="lineStyle">样式</param>
public void SetLineStyle(int row, int column, int lineStyle = 1)
{
SetLineStyle(GetRang(row, column), lineStyle);
}
/// <summary>
/// 是否自动换行
/// </summary>
/// <param name="range">所选单元格</param>
/// <param name="wrap">true：自动换行;false:不自动换行</param>
public void SetWrapText(Range range, bool wrap)
{
range.WrapText = wrap;
}
/// <summary>
/// 是否自动换行
/// </summary>
/// <param name="row">行索引</param>
/// <param name="column">列索引</param>
/// <param name="wrap">true：自动换行;false:不自动换行</param>
public void SetWrapText(int row, int column, bool wrap)
{
SetWrapText(GetRang(row, column), wrap);
}
/// <summary>
/// 单元格自动适应
/// </summary>
/// <param name="range">单元格</param>
public void SetAutoFit(Range range)
{
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
}
/// <summary>
/// 单元格自动适应
/// </summary>
/// <param name="row">行索引</param>
/// <param name="column">列索引</param>
public void SetAutoFit(int row, int column)
{
Range range = GetRang(row, column);
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
}
/// <summary>
/// 应用程序是否可见
/// </summary>
/// <param name="visible">true:可见;false:不可见</param>
public void SetExcelVisible(bool visible)
{
excel.Visible = visible;
}
/// <summary>
/// 设置打印预览
/// 注意：将应用程序设置为可见
/// </summary>
/// <param name="pageSize">纸张大小：默认A4</param>
/// <param name="orientation">纸张方向：默认为横向</param>
public void PrintPriview(XlPaperSize pageSize = XlPaperSize.xlPaperA4, XlPageOrientation orientation = XlPageOrientation.xlPortrait)
{
worksheet.PageSetup.PaperSize = pageSize;
worksheet.PageSetup.CenterHorizontally = true;
worksheet.PageSetup.CenterVertically = true;
worksheet.PageSetup.Orientation = orientation;//设置纸为横向
worksheet.PrintPreview(true);
}
/// <summary>
/// 打印当前选中的表单
/// 注意：设置好默认打印机
/// </summary>
public void PrintOut()
{
worksheet.PrintOutEx();
}
/// <summary>
/// 保存
/// </summary>
/// <param name="fullName">文件全路径</param>
/// <param name="alert">是否提示保存：默认不提示</param>
public void SaveXls(string fullName, bool alert = false)
{
excel.DisplayAlerts = alert;
workbook.SaveAs(fullName);
}
/// <summary>
/// 程序退出释放资源
/// </summary>
public void QuitExcel()
{
excel.Quit();
excel = null;
GC.Collect();
}
}