Excel操作类
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Reflection;
using Excel;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Diagnostics;
using System.Threading;
using first.Modules;
namespace first.Middle
{
/// <summary>
/// ExcelPrint 的摘要说明。
/// </summary>
public class ExcelPrint
{//Excel操作类
object missing=Missing.Value ;
private Excel.Application myexcel = new Excel.Application() ;
private FrmProsessNoBar bar = null;
//系统配置文件目录
private string AppPath = System.Windows.Forms.Application.StartupPath +"\\dbconfig.ini";
//模板文件的存放目录
private string _excelFolder = "";
public string ExcelFolder
{
get {return _excelFolder;}
set {_excelFolder = value;}
}
//xls文件复制的临时目录
private string _excelTemp = "";
public string ExcelTemp
{
get {return _excelTemp;}
set {_excelTemp = value;}
}
//被复制的xls模板文件全路径
private string _excelCopyName = "";
public string ExcelCopyName
{
get {return _excelCopyName;}
set {_excelCopyName = value;}
}
//复制完成后的xls临时文件全路径
private string _excelTempName = "";
public string ExcelTempName
{
get {return _excelTempName;}
set {_excelTempName = value;}
}
private string ExcelName = "";//模板名称
public ExcelPrint()
{
//
// TODO: 在此处添加构造函数逻辑
//
// this.ExcelFolder = IniInfo.getIniInfo(this.AppPath,"ExcelPrint","ExcelFolder");
// this.ExcelTemp = IniInfo.getIniInfo(this.AppPath,"ExcelPrint","ExcelTemp");
this.ExcelFolder = System.Windows.Forms.Application.StartupPath + "\\ExcelFolder\\";
this.ExcelTemp = System.Windows.Forms.Application.StartupPath + "\\ExcelTemp\\";
}
/// <summary>
/// 打开复制的XLS模板
/// </summary>
/// <param name="path">模板文件名</param>
public ExcelPrint(string fileName)
{
try
{
// this.ExcelFolder = IniInfo.getIniInfo(this.AppPath,"ExcelPrint","ExcelFolder");
// this.ExcelTemp = IniInfo.getIniInfo(this.AppPath,"ExcelPrint","ExcelTemp");
this.ExcelFolder = System.Windows.Forms.Application.StartupPath + "\\ExcelFolder\\";
this.ExcelTemp = System.Windows.Forms.Application.StartupPath + "\\ExcelTemp\\";
ExcelCopyName = this.ExcelFolder + fileName + ".xls";
ExcelTempName = this.ExcelTemp + fileName + getSystemTime() + ".xls";
ExcelName = fileName;
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 复制模板文件
/// </summary>
public bool ExcelCopy()
{
try
{
if (!isExistFolderOrFile())
{
return false;
}
else
{
File.Copy(@ExcelCopyName,@ExcelTempName,true);
}
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
return false;
}
return true;
}
public void OpenTempXls()
{
this.ThredShowBar();
//myexcel.Workbooks.Open (@ExcelTempName,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing); //2003 excel版本代码
myexcel.Workbooks.Open (@ExcelTempName,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing); //2000 excel版本代码
// myexcel.Visible = true;
myexcel.Visible = false;
}
public void SetXlsVisible()
{
if (bar != null)
{
bar.Close();
}
myexcel.Visible = true;
}
/// <summary>
/// 打开复制完成的xls的Temp文件
/// </summary>
public void ExcelOpen()
{
try
{
ExcelView exlView = new ExcelView(@ExcelTempName);
exlView.Show();
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 打印前判断文件和目录
/// </summary>
/// <returns></returns>
private bool isExistFolderOrFile()
{
try
{
if (!File.Exists(@ExcelCopyName))
{
MessageBox.Show("打印模板文件不存在！");
return false;
}
if (!System.IO.Directory.Exists(@ExcelTemp))
{
System.IO.Directory.CreateDirectory(@ExcelTemp);
}
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
return false;
}
return true;
}
/// <summary>
/// 取得系统时间字符串
/// </summary>
/// <returns></returns>
private string getSystemTime()
{
string tmp = "";
tmp = System.DateTime.Now.Year.ToString()
+ System.DateTime.Now.Month.ToString()
+ System.DateTime.Now.Day.ToString()
+ System.DateTime.Now.Hour.ToString()
+ System.DateTime.Now.Minute.ToString()
+ System.DateTime.Now.Second.ToString()
+ System.DateTime.Now.Millisecond.ToString();
return tmp;
}
/// <summary>
/// 修改指定表名
/// </summary>
/// <param name="newname"></param>
/// <param name="nub"></param>
public void SetSheetName(string newname,int nub)
{
try
{
Excel.Worksheet sheet = (Excel.Worksheet)myexcel.Worksheets .get_Item(nub);
sheet.Name =newname;
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 写入某单元格内容
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="valu"></param>
public void WriteOneCell(int row,int col,string valu)
{
try
{
myexcel.Cells[row,col]=valu;
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 另存为
/// </summary>
public void ExcelSave()
{
string exlnewname = this.ExcelTemp + ExcelName + getSystemTime() + ".xls";
myexcel.Save(exlnewname);
}
/// <summary>
/// 写入整个DATATABLE
/// </summary>
/// <param name="row"></param>
/// <param name="colstart"></param>
/// <param name="colend"></param>
/// <param name="dt"></param>
public void WriteTableValue(int row,System.Data.DataTable dt)
{
try
{
int datarow = dt.Rows.Count;//数据行数
int datacol = dt.Columns.Count ;// dataset列数
for(int i=0;i<datarow;i++)
{
for(int n=0;n<datacol;n++)
{
myexcel.Cells [row+i,n+1]=dt.Rows[i][n].ToString();
}
}
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 写入Excel并设置单元格边框
/// </summary>
/// <param name="row"></param>
/// <param name="dt"></param>
public void WriteTableValueFormat (int row,System.Data.DataTable dt)
{
try
{
int datarow = dt.Rows.Count;//数据行数
int datacol = dt.Columns.Count ;// dataset列数
for(int i=0;i<datarow;i++)
{
for(int n=0;n<datacol;n++)
{
myexcel.Cells [row + i,n + 1] = dt.Rows[i][n].ToString();
}
}
Excel.Range range = myexcel.get_Range(myexcel.Cells[row,1] , myexcel.Cells[row+datarow,datacol]);
range.Cells.Borders.LineStyle = 1;
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 写入整个DATATABLE,第一列为序号
/// </summary>
/// <param name="row"></param>
/// <param name="colstart"></param>
/// <param name="colend"></param>
/// <param name="dt"></param>
public void WriteTableValue(string type,int row,System.Data.DataTable dt)
{
try
{
int datarow = dt.Rows.Count;//数据行数
int datacol = dt.Columns.Count ;// dataset列数
for(int i=0;i<datarow;i++)
{
for(int n=0;n<datacol;n++)
{
if (n == 0)
{
int xuhao = i+1;
myexcel.Cells [row+i,n+1] = xuhao.ToString();
}
else
{
myexcel.Cells [row+i,n+1]=dt.Rows[i][n].ToString();
}
}
}
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 写入Excel 管理层电话表
/// </summary>
/// <param name="row"></param>
/// <param name="dt"></param>
public void WriteTableManagePhone(int row ,System.Data.DataTable dt)
{
try
{
int datarow = dt.Rows.Count;//数据行数
int datacol = dt.Columns.Count ;// dataset列数
Excel.Range FromRange = myexcel.get_Range(myexcel.Cells[row,1],myexcel.Cells[row+1,15]);
int k = 0;
for (int i = 0;i < datarow;i++)
{
k = 2 * i;
if(k != 0)
{
Excel.Range ToRange = myexcel.get_Range(myexcel.Cells[k+row,1],myexcel.Cells[k+row+1,15]);
FromRange.Copy(ToRange);
}
for (int m = 0;m < datacol;m++)
{
if(m <= 9)
{
myexcel.Cells[row+k,m+1] = dt.Rows[i][m].ToString();
}
if((m > 9) && (m < 13))
{
myexcel.Cells[row+k+1,m-2] = dt.Rows[i][m].ToString();
}
if((m >= 13) && (m <= 17))
{
myexcel.Cells[row+k,m-2] = dt.Rows[i][m].ToString();
}
if(m > 17)
{
myexcel.Cells[row+k+1,m-6] = dt.Rows[i][m].ToString();
}
}
}
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// copy excel某快区域到另外一块
/// </summary>
/// <param name="fromrowstart"></param>
/// <param name="fromrowend"></param>
/// <param name="fromcolstart"></param>
/// <param name="fromcolend"></param>
/// <param name="torowstart"></param>
/// <param name="torowend"></param>
/// <param name="tocolstart"></param>
/// <param name="tocolend"></param>
public void CopyRange(int fromrowstart,int fromrowend,int fromcolstart,int fromcolend,int torowstart,int torowend,int tocolstart,int tocolend)
{
try
{
int rownb =Math.Abs ( fromrowend-fromrowstart);//源 获取行数
int colnb =Math.Abs ( fromcolend-fromcolstart);//源 获取列数
int hrownb =Math.Abs ( torowend-torowstart);//目标 行数
int hcolnb =Math.Abs ( tocolend-tocolstart);//目标 列数
for (int i=0;i<=rownb && i<=hrownb;i++)
{
for(int n=0;n<=colnb && n<=hcolnb;n++)
{
myexcel.Cells[torowstart+i,tocolstart+n]=myexcel.Cells[fromrowstart+i,fromcolstart+n];
}
}
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 插入行
/// </summary>
/// <param name="sheetname">sheet名</param>
/// <param name="frow">第几行</param>
/// <param name="nb">插入几行</param>
public void InsertRow(string sheetname ,int frow ,int nb)
{
try
{
Excel.Worksheet sheet = (Excel.Worksheet)myexcel.Worksheets[sheetname];
Excel.Range range = (Excel.Range)sheet.Rows[frow,Missing.Value];
for(int i=0;i<nb;i++)
{
//range .Insert(Excel.XlDirection.xlDown,1); //2003 版本代码
range .Insert(Excel.XlDirection.xlDown); //2000 版本代码
}
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 插入列
/// </summary>
/// <param name="sheetname">sheet名</param>
/// <param name="fcol">第几列插入</param>
/// <param name="nb">插入几列</param>
public void InsertCol(string sheetname ,int fcol ,int nb)
{
try
{
Excel.Worksheet sheet = (Excel.Worksheet)myexcel.Worksheets[sheetname];
Excel.Range range = (Excel.Range)sheet.Columns[fcol,missing];
for(int i=0;i<nb;i++)
{
//range .Insert(Excel.XlDirection.xlToLeft,1); //2003代码
range .Insert(Excel.XlDirection.xlToLeft); //2000代码
}
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// copy整张表
/// </summary>
/// <param name="osheetname">源表名</param>
/// <param name="nsheetname">新表名</param>
public void CopySheet(string osheetname ,string nsheetname)
{
try
{
Excel.Worksheet sheet = (Excel.Worksheet)myexcel.Worksheets[osheetname];
int sheetcount = myexcel.Worksheets .Count ;//获取表的个数
sheet.Copy (Type.Missing,myexcel.Worksheets [sheetcount]);//放在最后
Excel.Worksheet sh = (Excel.Worksheet)myexcel.Worksheets[sheetcount+1];//取得复制的表
sh.Name=nsheetname;//更改表名
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 打印预览,预览指定表
/// </summary>
/// <param name="nb"></param>
public void PrintView(string fileName,int nb)
{
try
{
Excel.Worksheet sheet = (Excel.Worksheet)myexcel.Worksheets.get_Item(nb);
sheet.PrintPreview (missing);
myexcel.Quit();//隐藏EXCEL
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 打印预览,预览指定表
/// </summary>
/// <param name="nb"></param>
public void PrintView(string fileName)
{
try
{
Excel.Worksheet sheet = (Excel.Worksheet)myexcel.Worksheets.get_Item(1);
sheet.PrintPreview (missing);
myexcel.Quit();//隐藏EXCEL
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 打印预览,预览指定表
/// </summary>
/// <param name="nb"></param>
public void PrintView()
{
try
{
this.ExcelSave();
Excel.Worksheet sheet = (Excel.Worksheet)myexcel.Worksheets.get_Item(1);
sheet.PrintPreview (missing);
myexcel.Quit();//隐藏EXCEL
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 置工作表保护
/// </summary>
public void SetProtect()
{
// try
// {
// Excel.Worksheet sheet = (Excel.Worksheet)myexcel.Worksheets.get_Item(1);
// myexcel.Workbooks[1].Protect(Type.Missing,true,true);//设置工作簿保护（现设工作簿保护，再设工作表保护，不然不成功）
// sheet.Protect(missing,missing,missing,missing,true,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,false);
// sheet.SaveAs(@ExcelTempName,missing,missing,missing,missing,missing,missing,missing,missing,missing);
// }
// catch(System.Exception ex)
// {
// Console.Write(ex.Message);
// }
}
/// <summary>
/// 删除TEMP的EXL文件
/// </summary>
public void DelExl()
{
try
{
System.IO.DirectoryInfo DirInfo = new DirectoryInfo (@ExcelTemp);
foreach (System.IO.FileInfo file in DirInfo.GetFiles())
{
if(file.CreationTime.AddDays(1) <= DateTime.Now)
{
file.Delete();
}
}
}
catch(System.Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// 设置单元格合并、对齐
/// </summary>
/// <param name="FromRow"></param>
/// <param name="FromCol"></param>
/// <param name="ToRow"></param>
/// <param name="ToCol"></param>
public void SetFormat(int FromRow,int FromCol,int ToRow,int ToCol)
{
Excel.Range rang = myexcel.get_Range(myexcel.Cells[FromRow,FromCol],myexcel.Cells[ToRow,ToCol]);
rang.MergeCells = true;//合并单元格
rang.VerticalAlignment = Excel.Constants.xlCenter;//垂直居中
rang.HorizontalAlignment = Excel.Constants.xlRight;//水平靠右
}
private void ThredShowBar()
{
ThreadStart threadStart = new ThreadStart(showbar);
Thread thread = new Thread(threadStart);
thread.Start();
}
private void showbar()
{
bar = new FrmProsessNoBar();
bar.ShowDialog();
}
}
}
