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);
}
private void button1_Click(object sender, EventArgs e)
{
saveFileDialog1.Title = "序列号生成工具----百思威科技";
saveFileDialog1.Filter = "Excel(*.xls)|*.xls";
saveFileDialog1.FileName = string.Format("序列号_{0}", DateTime.Now.ToString("yyyyMMdd"));
DialogResult result = saveFileDialog1.ShowDialog();
Excel._Application xlapp = new Excel.Application();
Excel.Workbook xlbook = xlapp.Workbooks.Add(true);
Excel.Worksheet xlsheet = (Excel.Worksheet)xlbook.Worksheets[1];
int RowCount = Convert.ToInt32(len.Text);
int RowIndex = 0;
for (int i = 0; i < RowCount; i++)
{
RowIndex++;
xlsheet.Cells[RowIndex, 1] = "SN:" + (Convert.ToInt32(sarial.Text) + i).ToString();
}
xlbook.Saved = true;
xlbook.SaveCopyAs(saveFileDialog1.FileName);
xlapp.Quit();
MessageBox.Show("导出成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// <summary>
/// 把DataTable中的数据导出到Excel
/// </summary>
/// <param name="dt"></param>
public static void DataTableToExcel(System.Data.DataTable srcDt, string savename)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt = srcDt;
if (dt == null)
{
return;
}
string saveFileName = "";
bool fileSaved = false;
/*SaveFileDialog saveDialog = new SaveFileDialog();
* saveDialog.DefaultExt = "xlsx";
* saveDialog.Filter = "Excel文件|*.xlsx";
* saveDialog.FileName = "导出文件";
* saveDialog.ShowDialog();
* saveFileName = saveDialog.FileName;
* if (saveFileName.IndexOf(":") < 0) return; //被点了取消 */
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).Font.ColorIndex = 9;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).ColumnWidth = 40;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 1]).RowHeight = 50;
//worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]).Interior.ColorIndex = 6;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).WrapText = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
for (int i = 2; i < 12; i++)
{
if (dt.Rows[i - 2][3].ToString() == "E")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 3;
}
if (dt.Rows[i - 2][3].ToString() == "W")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 6;
}
}
//写入字段
worksheet.Cells[1, 1] = "参数名称";
worksheet.Cells[1, 2] = "参数值";
worksheet.Cells[1, 3] = "推荐值";
worksheet.Cells[1, 4] = "报警值";
worksheet.Cells[1, 5] = "详情";
worksheet.Cells[1, 6] = "参数类型";
worksheet.Cells[1, 7] = "重要级别";
worksheet.Cells[1, 8] = "历史变化";
//写入数值
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];
}
System.Windows.Forms.Application.DoEvents();
}
//worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
/*string md5 = "";
* for (int r = 0; r < 5; r++)
* {
* for (int i = 0; i < 8; i++)
* {
* md5 += dt.Rows[r][i].ToString();
* worksheet.Cells[2, 9] = md5;
* }
*
* }
*/
//worksheet.Cells.Width = 39;
//if (saveFileName != "")
//{
try
{
workbook.Saved = true;
saveFileName = savename;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
MessageBox.Show("导出完成！");
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
//}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
}
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);
}
}
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>");
}
