/// <summary>
/// 关闭Excel
/// </summary>
public void Close()
{
myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
/// <summary>
/// 保存
/// </summary>
/// <param name="xlsSheet"></param>
/// <param name="xlsBook"></param>
/// <param name="xlsSheetName"></param>
private string SaveAsExcel(Excel.Worksheet xlsSheet, Excel.Workbook xlsBook)
{
//string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) ;
string desktopPath = @"C:\NPOI";
if (System.IO.Directory.Exists(desktopPath) == false)
{
System.IO.Directory.CreateDirectory(desktopPath);
}
desktopPath = desktopPath + "\\" + "IQC";
if (System.IO.Directory.Exists(desktopPath) == true)
{
System.IO.Directory.Delete(desktopPath);
}
xlsSheet.SaveAs(desktopPath, 56); //保存在
////OpenXls(desktopPath);
//PrinttheExcel(xlsSheet, desktopPath);
xlsBook.Close();
xlsSheet = null;
KillProcess("Excel");
string dd = System.IO.Path.GetExtension(desktopPath);
return(desktopPath + ".xls");
}
//关闭一个Excel对象，销毁对象
public void Close()
{
//wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
private void createExcel()
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;
try
{
FileInfo file = new FileInfo(fileLoc);
if (file.Exists)
{
excelApp = new Excel.Application();
workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
sheets = workbook.Sheets;
//check columns exist
foreach (Excel.Worksheet sheet in sheets)
{
Console.WriteLine(sheet.Name);
sheet.Select(Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = "My New Sheet";
newSheet.Cells[1, 1] = "BOO!";
workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
}
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
newSheet = null;
sheets = null;
workbook = null;
excelApp = null;
GC.Collect();
}
}
public static void Merge(string sourceFile, string baseFile, string targetFile, int rowCount, int columnCount)
{
object MISSING = Type.Missing;
Excel.Application excel = new Excel.Application();
try
{
Excel.Workbook sourceBook = excel.Workbooks.Open(sourceFile,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
Excel.Worksheet sourceSheet = (Excel.Worksheet)sourceBook.Sheets[1];
Excel.Workbook baseBook = excel.Workbooks.Open(baseFile,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
Excel.Worksheet baseSheet = (Excel.Worksheet)baseBook.Sheets[1];
//baseSheet.Unprotect(string.Empty);
for (int column = 1; column <= columnCount; column++)
{
for (int row = 1; row <= rowCount; row++)
{
Excel.Range baseRange = (Excel.Range)baseSheet.Cells[row, column];
if (baseRange.AllowEdit)
{
Excel.Range sourceRange = (Excel.Range)sourceSheet.Cells[row, column];
baseRange.Value2 = sourceRange.Value2;
}
}
}
//baseSheet.Protect(string.Empty,
// MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
// MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING);
sourceBook.Close(false, MISSING, MISSING);
baseBook.SaveCopyAs(targetFile);
baseBook.Close(false, MISSING, MISSING);
}
finally
{
Office.ExcelHelper.Kill(excel);
}
Thread.Sleep(100);
}
//字母-〉列号 Z-〉26
//public void SaveAsExcel()
//{
// if (xlsSaveFileName == "")
// {
// throw new Exception("未指定要保存的文件名");
// }
// try
// {
// xlsApp.Visible = true;
// xlsWb.PrintPreview(false);
// xlsWb = null;
// xlsApp.Quit();
// xlsApp = null;
// //xlsWs.SaveAs(xlsSaveFileName, Excel.XlFileFormat.xlExcel7,
// // Type.Missing, Type.Missing,
// // Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// // Type.Missing, Type.Missing);
// }
// catch (Exception e)
// {
// CloseExcelApplication();
// throw new Exception(e.Message);
// }
//}
public void SaveAsExcel(bool IsView)
{
try
{
if (File.Exists(xlsSaveFileName))
{
File.Delete(xlsSaveFileName);
}
if (IsView)
{
xlsApp.Visible = true;
xlsWb.PrintPreview(false);
}
xlsWb.Close(true, xlsSaveFileName, Type.Missing);
xlsWb = null;
xlsApp.Quit();
xlsApp = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
/// <summary>
/// 将datatable导出到excel
/// </summary>
/// <param name="dt"></param>
/// <param name="saveasfilepath"></param>
/// <param name="WorkbookName"></param>
public void ExportExcel(System.Data.DataTable dt, string saveasfilepath, string WorkbookName)
{
if (dt == null)
{
return;
}
string saveFileName = saveasfilepath; // "d:\\333.xlsx";
bool fileSaved = false;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的机子未安装Excel");
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
if (!string.IsNullOrEmpty(WorkbookName))
{
Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
ws.Name = WorkbookName;
}
Excel.Range range;
//string oldCaption = DateTime.Today.ToString("yy-MM-dd");
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
// worksheet.Cells[1, 1] = DateTime.Today.ToString("yy-MM-dd");
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
//this.lbl_process.Text = "正在导出数据[" + percent.ToString("0.00") + "%]..."; //这里可以自己做一个label用来显示进度.
System.Windows.Forms.Application.DoEvents();
}
//this.lbl_process.Visible = false;
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
if (saveFileName != "")
{
try
{
//xlApp.Visible = true;
//workbook.PrintPreview(false);
workbook.Close(true, saveFileName, Type.Missing);
workbook = null;
//SaveAs方法会截断超过255个字符的字符串
//workbook.Saved = true;
//workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlExcel7, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing,
// Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing);
//fileSaved = true;
}
catch (Exception ex)
{
xlApp.Quit();
GC.Collect();
fileSaved = false;
throw new Exception("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
xlApp = null;
GC.Collect();//强行销毁
if (fileSaved && File.Exists(saveFileName))
{
//System.Diagnostics.Process.Start(saveFileName);
}
}
public void DataSet2Excel(DataSet ds, Dictionary <string, string> dicColumnNameMapping, string fileName)
{
if (ds == null || ds.Tables.Count == 0)
{
return;
}
if (ValidateIsInstallExcel() == false)
{
return;
}
#region 自动适应超出6万行就分Sheet
int maxcount = 60000;
for (int count = 0; count < ds.Tables.Count; count++)
{
if (ds.Tables[count].Rows.Count > maxcount)
{
int zs = ds.Tables[count].Rows.Count;
int j = zs / maxcount;
int j1 = zs % maxcount;
if (j1 > 0)
{
j = j + 1;
}
for (int a = 0; a < j; a++)
{
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 0; i < ds.Tables[count].Columns.Count; i++)
{
System.Data.DataColumn dc = new System.Data.DataColumn(ds.Tables[count].Columns[i].ColumnName);
dt.Columns.Add(dc);
}
int qs = a * maxcount;
int js = qs + maxcount;
if (js > zs)
{
js = zs;
}
for (int m = qs; m < js; m++)
{
System.Data.DataRow dr = dt.NewRow();
for (int n = 0; n < dt.Columns.Count; n++)
{
dr[n] = ds.Tables[count].Rows[m][n];
}
dt.Rows.Add(dr);
}
dt.TableName = ds.Tables[count].TableName + (a + 1).ToString();
ds.Tables.Add(dt);
}
ds.Tables.Remove(ds.Tables[count]);
count--;
}
}
#endregion 自动适应超出6万行就分Sheet
try
{
int totalCount = 0;
int currentCount = 0;
for (int n = 0; n < ds.Tables.Count; n++)
{
totalCount += ds.Tables[n].Rows.Count;
}
object omissing = System.Reflection.Missing.Value;
Excel.ApplicationClass xlapp = new Excel.ApplicationClass();
xlapp.DefaultFilePath = "";
xlapp.DisplayAlerts = true;
xlapp.SheetsInNewWorkbook = 1;
Excel.Workbook xlworkbook = xlapp.Workbooks.Add(omissing);
for (int i = ds.Tables.Count - 1; i >= 0; i--)
{
System.Data.DataTable tmpDataTable = ds.Tables[i];
currentCount = DataTable2Excel(tmpDataTable, totalCount, currentCount, dicColumnNameMapping, omissing, xlworkbook);
}
if (!string.IsNullOrEmpty(fileName))
{
xlworkbook.SaveCopyAs(fileName);
xlworkbook.Close(false, null, null);
xlapp.Quit();
}
else
{
xlapp.Visible = true;
}
//System.Runtime.InteropServices.Marshal.ReleaseComObject(xlworkbook);
ReleaseExcelObject(xlworkbook, xlapp);
}
catch (Exception ex)
{
GC.Collect();
AddError(ex.Message);
}
}
public static void CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
{
//文件存在时先删除文件后再进行下一步操作
FileInfo file = new FileInfo(strFullFilePath);
if (file.Exists)
{
file.Delete();
}
int rowIndex = 3; //开始写入数据的单元格行
int colIndex = 0; //开始写入数据的单元格列
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.ApplicationClass mExcel = new Excel.ApplicationClass();
mExcel.Visible = false;
Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks;
Excel.Workbook mBook = (Excel.Workbook)(mBooks.Add(miss));
Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet;
Excel.Range er = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
er.Value2 = title;
try
{
foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格
{
colIndex++;
mSheet.Cells[3, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows) //同样方法处理数据
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
if (colIndex == 2)
{
mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
}
else
{
mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//保存工作已写入数据的工作表
mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
// return true;
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
finally //finally中的代码主要用来释放内存和中止进程()
{
mBook.Close(false, miss, miss);
mBooks.Close();
mExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
GC.Collect();
}
//return false;
}
/// <summary>
/// 将二维数组数据写入Excel文件（套用模板并分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2...”</param>
public void ArrayToExcel(string[,] arr, int rows, int top, int left, string sheetPrefixName)
{
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
int sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
DateTime beforeTime;
DateTime afterTime;
if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
{
sheetPrefixName = "Sheet";
}
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打开模板文件，得到WorkBook对象
Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet对象
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
}
#region 将二维数组数据写入Excel
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
{
endRow = rowCount;
}
//获取要写入数据的WorkSheet对象，并重命名
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
sheet.Name = sheetPrefixName + "-" + i.ToString();
//将二维数组中的数据写入WorkSheet
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
sheet.Cells[top + j, left + k] = arr[startRow + j, k];
}
}
Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
txtAuthor.Text = "lingyun_k";
txtDate.Text = DateTime.Now.ToShortDateString();
txtVersion.Text = "1.0.0.0";
}
#endregion
//输出Excel文件并退出
try
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null;
workBook = null;
app = null;
GC.Collect();
}
catch (Exception e)
{
throw e;
}
finally
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID，暂时只能判断进程启动时间
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
}
private void PrintSaleInvoice(System.Data.DataTable dt)
{
try
{
int PageSize = 25;
int TotalPages = dt.Rows.Count / 25;
int remainder = dt.Rows.Count % 25;
if (remainder > 0)
{
TotalPages++;
}
//FillInvoiceHeader(TotalPages);
int iStart = 14;
int pos = 0;
int row = 0;
m_TotalDiscount = 0;
for (int page = 0; page < TotalPages; page++)
{
if (page > 0)
{
iStart = 51;
}
OpenTemplate();
FillInvoiceHeader(page + 1, TotalPages);
for (int index = 0; index < PageSize; index++)
{
if (row >= dt.Rows.Count)
{
break;
}
pos = iStart + index;
FillCellsWithData(dt, pos, row);
row++;
}
if (page == TotalPages - 1) //Last page
{
if (TotalPages == 1)
{
pos = 39;
}
else
{
pos = 76;
}
string TotDiscountValue = "F" + pos.ToString();
Excel.Range TotDiscountValueCell = (Excel.Range)excelWorksheet.get_Range(TotDiscountValue, TotDiscountValue);
TotDiscountValueCell.Value2 = m_TotalDiscount.ToString();
pos++;
string TotalValue = "G" + pos.ToString();
Excel.Range TotalValueCell = (Excel.Range)excelWorksheet.get_Range(TotalValue, TotalValue);
TotalValueCell.Value2 = m_InvoiceTotal.ToString();
}
//excelWorkbook.SaveCopyAs("C:\\DEV\\MICS\\TEMPLATES\\inv" + page + ".xls");
if (page == 0)
{
excelWorksheet.PrintOut(1, 1, m_Copies, false, "", false, false, "");
}
else
{
excelWorksheet.PrintOut(2, 2, m_Copies, false, "", false, false, "");
}
excelWorkbook.Close(false, m_TemplatePath, true);
}
}
catch (Exception ex)
{
excelWorkbook.Close(false, m_TemplatePath, true);
throw(ex);
}
}
/// <summary>
/// 多张报表合成一个EXCEL文件
/// </summary>
/// <param name="tempDir">临时文件存放目录</param>
/// <param name="filename">指定导出的文件名，不指定的情况下，弹出对话框让用户选择</param>
/// <param name="sheetNames">工作表名列表</param>
/// <param name="url">报表服务器路径</param>
/// <param name="path">报表路径</param>
/// <param name="parms">参数列表</param>
/// <param name="isVisible">是否显示EXCEL，若不显示EXCEL，则自动保存到filename</param>
/// <remarks>
/// 最后清除临时文件
/// </remarks>
public static void ExportToOneExcelSave(string tempDir, string filename, string[] sheetNames, string url, string[] path,
ReportParameter[][] parms, bool isVisible)
{
if (filename == string.Empty)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "EXCEL(*.xls)|*.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
filename = sfd.FileName;
}
if (sfd.FileName == string.Empty)
{
return;
}
}
string[] excels = new string[path.Length];
string dt = DateTime.Now.ToString("yyyyMMddHHmmssfff");
for (int j = 0; j < excels.Length; j++)
{
excels[j] = string.Format(@"{0}\{1}{2}.xls", tempDir, dt, j);
}
for (int i = 0; i < path.Length; i++)
{
Export("EXCEL", excels[i], string.Empty, url, path[i],
(parms == null ? null : parms[i]));
}
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook1 = excel.Workbooks.Open(excels[0], Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Sheets[1];
worksheet1.Name = sheetNames[0];
worksheet1.PageSetup.CenterFooter = "第 &P 页，共 &N 页"; //加页码
for (int k = excels.Length - 1; k >= 1; k--)
{
Excel.Workbook workbook2 = excel.Workbooks.Open(excels[k], Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet worksheet2 = (Excel.Worksheet)workbook2.Sheets[1];
worksheet2.Name = sheetNames[k];
worksheet2.PageSetup.CenterFooter = "第 &P 页，共 &N 页"; //加页码
worksheet2.Copy(Type.Missing, worksheet1);
workbook2.Close(false, Type.Missing, Type.Missing);
}
if (!isVisible)
{
workbook1.Save();
Office.ExcelHelper.Kill(excel);
if (File.Exists(tempDir + filename))
{
File.Delete(tempDir + filename);
}
Thread.Sleep(100);
File.Move(excels[0], tempDir + filename);
}
for (int m = 1; m < excels.Length; m++)
{
File.Delete(excels[m]);
}
//excel.Visible = isVisible;
}
private void ExportUpEmployee()
{
string strSql = @"select GetOrgName(GetStationOrgID(b.org_Id)) UnitName,
getworkshopname(b.org_id) WorkShopName,
case when c.level_num=4 then c.Short_Name else null end WorkGroupName,
Employee_Name, Identity_CardNo,
e.Train_Plan_Name,
d.Class_Name,f.Post_Name
from zj_train_plan_employee t
inner join Employee b on t.Employee_ID=b.Employee_ID
inner join Org c on b.Org_ID=c.Org_ID
inner join ZJ_Train_Plan_Post_Class d on t.Train_Plan_Post_Class_ID=d.Train_Plan_Post_Class_ID
inner join ZJ_Train_Plan e on d.Train_Plan_ID=e.Train_Plan_ID
inner join Post f on b.Post_ID = f.Post_ID
where t.Train_Plan_Post_Class_Org_ID=" + Request.QueryString.Get("classOrgID");
OracleAccess db = new OracleAccess();
DataSet ds = db.RunSqlDataSet(strSql);
if (ds.Tables[0].Rows.Count == 0)
{
Response.Write("<script>alert('没有已上报的员工信息！');window.close();</script>");
return;
}
string templateFileName = Server.MapPath("/RailExamBao/RandomExam/ProgressBar.htm");
StreamReader reader = new StreamReader(@templateFileName, System.Text.Encoding.GetEncoding("gb2312"));
string html = reader.ReadToEnd();
reader.Close();
Response.Write(html);
Response.Flush();
System.Threading.Thread.Sleep(200);
string jsBlock;
Excel.Application objApp = new Excel.ApplicationClass();
Excel.Workbooks objbooks = objApp.Workbooks;
Excel.Workbook objbook = objbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet objSheet = (Excel.Worksheet)objbook.Worksheets[1]; //取得sheet1
string filename = "";
string strName = "";
string strFileName = "";
try
{
int count = ds.Tables[0].Rows.Count;
strName = "Excel";
strFileName = ds.Tables[0].Rows[0]["UnitName"].ToString() + ds.Tables[0].Rows[0]["Class_Name"].ToString() + "上报人员";
filename = Server.MapPath("/RailExamBao/Excel/" + strName + ".xls");
objSheet.Cells.Font.Size = 10;
objSheet.Cells.Font.Name = "宋体";
int colIndex = 1;
objSheet.Cells[1, colIndex] = "序号";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "站段";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "车间";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "班组";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "姓名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "身份证号";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "职名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "上报培训计划名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[1, colIndex] = "上报计划培训班名";
((Excel.Range)objSheet.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Threading.Thread.Sleep(10);
jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(1 * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
int rowIndex = 2;
foreach (DataRow dr in ds.Tables[0].Rows)
{
colIndex = 1;
objSheet.Cells[rowIndex, colIndex] = rowIndex - 1;
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["UnitName"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["WorkShopName"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["WorkGroupName"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Employee_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = "'" + dr["Identity_CardNo"];
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Post_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Train_Plan_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
colIndex++;
objSheet.Cells[rowIndex, colIndex] = dr["Class_Name"].ToString();
((Excel.Range)objSheet.Cells[rowIndex, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
System.Threading.Thread.Sleep(10);
jsBlock = "<script>SetPorgressBar('导出培训计划上报人员','" + ((double)(rowIndex * 100) / (double)count + 1).ToString("0.00") + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
rowIndex++;
}
objSheet.Cells.Columns.AutoFit();
objApp.Visible = false;
objbook.Saved = true;
objbook.SaveCopyAs(filename);
// 处理完成
jsBlock = "<script>SetCompleted('处理完成。'); </script>";
Response.Write(jsBlock);
Response.Flush();
}
catch (Exception ex)
{
throw ex;
}
finally
{
objbook.Close(Type.Missing, filename, Type.Missing);
objbooks.Close();
objApp.Application.Workbooks.Close();
objApp.Application.Quit();
objApp.Quit();
GC.Collect();
}
Response.Write("<script>top.returnValue='" + strName + "|" + strFileName + "';window.close();</script>");
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public static bool saveDtToExcel(System.Data.DataTable dt, String filePath)
{
ApplicationClass app = new ApplicationClass();
app.Visible = false;
Workbook wBook = app.Workbooks.Add(true);
Worksheet wSheet = (Worksheet)wBook.Worksheets[1];
if (dt == null)
{
MessageBox.Show("数据源为空，无法导出。", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
//修改wSheet的名称。
if (!String.IsNullOrEmpty(dt.TableName))
{
wSheet.Name = dt.TableName;
}
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
//写标题
try
{
//每行格式设置，注意标题占一行。
Range range = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[rowCount + 1, colCount + 1]);
//设置单元格为文本。
range.NumberFormatLocal = "@";
//水平对齐方式
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
for (int j = 0; j < colCount; j++)
{
wSheet.Cells[1, j + 1] = dt.Columns[j].ColumnName.ToString();
}
if (rowCount > 0)
{
//逐行写数据。
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
String str = dt.Rows[i][j].ToString();
wSheet.Cells[i + 2, j + 1] = str;
}
}
}
//自动调整列宽
range.EntireColumn.AutoFit();
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存excel文档并关闭
wBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wBook.Close(true, filePath, Type.Missing);
//退出Excel程序
app.Quit();
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//调用GC的垃圾收集方法
GC.Collect();
GC.WaitForPendingFinalizers();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "提示消息:", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
finally
{
}
}
/// <summary>
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
public static void DataGridViewToExcel(MultiColHeaderDgv dgv)
{
#region
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Excel.Application objExcel = new Excel.Application();
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Excel.ApplicationClass();
//objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
//objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
int displayColumnsCount = 1;
//Excel.Range
Excel.Range range1 = objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[3, 1]);
Excel.Range range2 = objExcel.get_Range(objExcel.Cells[1, 2], objExcel.Cells[3, 2]);
Excel.Range range3 = objExcel.get_Range(objExcel.Cells[1, 3], objExcel.Cells[3, 3]);
Excel.Range range4 = objExcel.get_Range(objExcel.Cells[1, 4], objExcel.Cells[3, 4]);
Excel.Range range5 = objExcel.get_Range(objExcel.Cells[1, 5], objExcel.Cells[3, 5]);
Excel.Range range6 = objExcel.get_Range(objExcel.Cells[1, 6], objExcel.Cells[3, 6]);
Excel.Range range7 = objExcel.get_Range(objExcel.Cells[1, 7], objExcel.Cells[3, 7]);
Excel.Range range8 = objExcel.get_Range(objExcel.Cells[2, 8], objExcel.Cells[3, 8]);
Excel.Range range9 = objExcel.get_Range(objExcel.Cells[1, 8], objExcel.Cells[1, 11]);
Excel.Range range10 = objExcel.get_Range(objExcel.Cells[2, 9], objExcel.Cells[2, 11]);
range1.Merge(0);
range2.Merge(0);
range3.Merge(0);
range4.Merge(0);
range5.Merge(0);
range6.Merge(0);
range7.Merge(0);
range8.Merge(0);
range9.Merge(0);
range9.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range10.Merge(0);
range10.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range1.NumberFormatLocal = "@"; //设置单元格格式为文本
range2.NumberFormatLocal = "@"; //设置单元格格式为文本
range3.NumberFormatLocal = "@"; //设置单元格格式为文本
range4.NumberFormatLocal = "@"; //设置单元格格式为文本
range5.NumberFormatLocal = "@"; //设置单元格格式为文本
range6.NumberFormatLocal = "@"; //设置单元格格式为文本
range7.NumberFormatLocal = "@"; //设置单元格格式为文本
range8.NumberFormatLocal = "@"; //设置单元格格式为文本
range9.NumberFormatLocal = "@"; //设置单元格格式为文本
objsheet.Cells[1, 1] = "财务分类";
objsheet.Cells[1, 2] = "项目代码";
objsheet.Cells[1, 3] = "项目名称";
objsheet.Cells[1, 4] = "项目内涵";
objsheet.Cells[1, 5] = "除外内容";
objsheet.Cells[1, 6] = "计价单位";
objsheet.Cells[1, 7] = "说明";
objsheet.Cells[2, 8] = "省定价";
objsheet.Cells[3, 9] = "三档";
objsheet.Cells[3, 10] = "二档";
objsheet.Cells[3, 11] = "一档";
objsheet.Cells[1, 8] = "价格（元）";
objsheet.Cells[2, 9] = "市定价格";
range2.ColumnWidth = 12; //设置单元格的宽度
range3.ColumnWidth = 20; //设置单元格的宽度
range4.ColumnWidth = 35; //设置单元格的宽度
range5.ColumnWidth = 25; //设置单元格的宽度
range7.ColumnWidth = 25; //设置单元格的宽度
range2.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range3.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range4.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range5.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range7.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
Excel.Range rangGol = objsheet.get_Range("A1", "K" + dgv.RowCount.ToString());
rangGol.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
rangGol.WrapText = true;
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
//tempProgressBar.PerformStep();
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + 4, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}