private void creat_Click(object sender, EventArgs e)
{
// textBox2.Text = UInt96.Parse(textBox1.Text).Inc().ToString();
saveFileDialog1.Title = "MAC地址生成工具----百思威科技";
saveFileDialog1.Filter = "Excel(*.xls)|*.xls";
saveFileDialog1.FileName = string.Format("MAC地址_{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];
// MessageBox.Show(textBox2.Text);
int RowCount = Convert.ToInt32(textBox2.Text);
int RowIndex = 0;
string val = textBox1.Text;
val = val.Replace(":", "");
for (int i = 0; i < RowCount; i++)
{
RowIndex++;
if (i != 0)
{
if (radioButton1.Checked)
{
val = UInt96.Parse(val).Inc().ToString(":");
}
else
{
val = UInt96.Parse(val).Inc().ToString("");
}
xlsheet.Cells[RowIndex, 1] = val;
}
else
{
if (radioButton1.Checked)
{
string[] sArray = Regex.Split(val, @"(\w{2})");
List <string> listTemp = new List <string>();
foreach (string s in sArray)
{
if (string.IsNullOrEmpty(s))
{
continue;
}
listTemp.Add(s);
}
string[] newlist = listTemp.ToArray();
val = String.Join(":", newlist);
}
xlsheet.Cells[RowIndex, 1] = val;
}
}
xlbook.Saved = true;
xlbook.SaveCopyAs(saveFileDialog1.FileName);
xlapp.Quit();
MessageBox.Show("导出成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// <summary>
///
/// </summary>
private void InputExcel()
{
// if(m_dt.Rows.Count==0)
// else
// {
// m_exl=new Excel.ApplicationClass();
// m_exl.Workbooks.Add ( true );
// for(int j=0;j<m_dt.Columns.Count;j++)
// {
// for(int z=0;z<m_Title.Length;z++)
// {
// if(m_dt.Columns[j].ColumnName==m_Title[z].name)
// {
// m_exl.Cells[2,j+1]=m_Title[z].title;
// for(int i=0;i<m_dt.Rows.Count;i++)
// {
// m_exl.Cells[i+3,j+1]=m_dt.Rows[i][j];
// }
// break;
// }
//
// }
// continue;
// }
// m_exl.Visible=true;
// }
// else
// {
string str = Path.GetDirectoryName(Application.ExecutablePath) + "\\report\\GRDB.xls";
m_exl = new Excel.ApplicationClass();
// m_exl.Workbooks.Add(true);
Excel.Workbook eWork = m_exl.Workbooks.Add(str); //true)
eWork.SaveCopyAs("ll");
m_exl.Cells[2, 1] = m_time;
for (int i = 1; i < m_Title.Length; i++)
{
for (int j = 0; j < m_dt.Columns.Count; j++)
{
if (m_dt.Columns[j].ColumnName == m_Title[i].name)
{
// m_exl.Cells[2,i]=m_Title[i].title;
for (int z = 0; z < m_dt.Rows.Count; z++)
{
m_exl.Cells[z + 5, i] = m_dt.Rows[z][j];
}
}
}
}
m_exl.Visible = true;
// }
}
protected void BusiIncomeExportToExcel(string tabname, string tabdate, DataTable dtIncome)
{
try
{
Excel.Application xapp = new Excel.ApplicationClass();
Excel.Workbook xbook = xapp.Workbooks.Open(Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet xSheet = (Excel.Worksheet)xbook.Sheets["业务量"]; //得到Sheet
xSheet.get_Range("A1", Missing.Value).Value2 = tabname;
xSheet.get_Range("A2", Missing.Value).Value2 = tabdate;
for (int i = 1; i < dtIncome.Rows.Count - 2; i++)
{
for (int j = 1; j < 8; j++)
{
xSheet.Cells[i + 3, j + 1] = dtIncome.Rows[i][j].ToString();
}
}
for (int i = 1; i < 8; i++)
{
xSheet.Cells[21, i + 1] = dtIncome.Rows[18][i].ToString();
}
SaveFileDialog SaveFileDialog1 = new SaveFileDialog();
SaveFileDialog1.Filter = "Excel文件（*.xls）|*.xls";
SaveFileDialog1.FileName = SysInitial.CP + "业务量报表" + DateTime.Now.ToShortDateString() + ".xls";
if (SaveFileDialog1.ShowDialog() == DialogResult.OK)
{
xbook.SaveCopyAs(SaveFileDialog1.FileName); //另存
xbook.Close(false, Application.StartupPath + @"\BusiIncomeModel.xls", Missing.Value); //关闭
xSheet = null;
xbook = null;
xapp.Quit();
xapp = null;
}
else
{
xbook.Close(false, Missing.Value, Missing.Value); //关闭
xSheet = null;
xbook = null;
xapp.Quit();
xapp = null;
}
}
catch (Exception err)
{
MessageBox.Show("导出时出错，请重试！", "系统提示", MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
clog.WriteLine(err);
}
finally
{
}
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="selectstring">选择导出数据的SQL选择语句</param>
public static void DataOut(SaveFileDialog dialog, string selectstring)
{
if (dialog.ShowDialog() == DialogResult.OK)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
worksheet.Cells.NumberFormatLocal = "@";
///////////////////////////////////
SqlConnection connect = InitConnect.GetConnection();
SqlDataReader read = null;
try
{
connect.Open();
SqlCommand cmd = new SqlCommand(selectstring, connect);
read = cmd.ExecuteReader();
for (int i = 0; i < read.FieldCount; i++)
{
worksheet.Cells[1, i + 1] = read.GetName(i).Trim();
}
int row = 2;
int count = 0;
while (read.Read())
{
count++;
for (int i = 0; i < read.FieldCount; i++)
{
worksheet.Cells[row, i + 1] = read[i].ToString().Trim();
}
row++;
}
MessageBox.Show("成功导出" + count.ToString() + "条记录！", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
catch (Exception ee)
{
MessageBox.Show("错误：" + ee.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
finally
{
read.Close();
connect.Close();
object change = false, filename = dialog.FileName;
workbook.SaveCopyAs(filename);
workbook.Close(change, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excel.Quit();
}
}
}
/// <summary>
/// 将DataTable中的列名及数据导出到Excel表中
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
public void DataTabletoExcelkk(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
Excel.Application xlApp = new Excel.ApplicationClass();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet ws = (Excel.Worksheet)xlBook.Worksheets[1];
int colnum = tmpDataTable.Columns.Count;
Excel.Range r = ws.get_Range("A1", NumToExeclRowStr(colnum) + "1");
object[] objHeader = new object[colnum];
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable.Columns)
{
objHeader[columnIndex] = dc.ColumnName;
columnIndex++;
}
r.Value2 = objHeader;
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
objHeader[columnIndex] = tmpDataTable.Rows[i][j].ToString();
columnIndex++;
}
r = ws.get_Range("A" + (i + 2), NumToExeclRowStr(colnum) + (i + 2));
r.Value2 = objHeader;
}
r.EntireColumn.AutoFit();
xlBook.SaveCopyAs(strFileName);
}
private static void excelSaveClose(string szPath, string szResultPath, Excel.Application rExcel, Excel.Workbook rWorkbook)
{
Process[] localByNameApp = Process.GetProcessesByName(szPath);//获取程序名的所有进程
if (localByNameApp.Length > 0)
{
foreach (var app in localByNameApp)
{
// if (!app.HasExited)
// {
#region
////设置禁止弹出保存和覆盖的询问提示框
rExcel.DisplayAlerts = false;
rExcel.AlertBeforeOverwriting = false;
rExcel.Visible = false;
//wb.Saved = true;
////保存工作簿
//rExcel.Application.Workbooks.Add(true).Save();
//保存excel文件
///excel.Save("E:\\c#_test\\winFormTest\\winFormTest\\hahaha.xls");
//确保Excel进程关闭
rExcel.Quit();
rExcel = null;
#endregion
app.Kill();//关闭进程
/*}*/
}
}
if (rWorkbook != null)
{
rExcel.DisplayAlerts = false;
rExcel.AlertBeforeOverwriting = false;
rExcel.Visible = false;
//rExcel.Application.Workbooks.Add(true).Save();
//保存结果
rWorkbook.SaveCopyAs(szResultPath);
///*, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing*/);
//wb.Save();
rWorkbook.Close(false, szPath, szPath);
}
rExcel.Quit();
// 安全回收进程
System.GC.GetGeneration(rExcel);
}
/// <summary>
/// 将DataTable中的列名及数据导出到Excel表中
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
Excel.Application xlApp = new Excel.ApplicationClass();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
}
}
xlBook.SaveCopyAs(strFileName);
}
/// <summary>
/// 将datatable导出excel文件
/// </summary>
/// <param name="dt">需要导出的datatable</param>
/// <param name="AbosultedFilePath">导出文件的绝对路径</param>
/// <returns></returns>
public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
dt.Columns.Remove("id");
dt.Columns.Remove("year");
dt.Columns.Remove("month");
dt.Columns.Remove("jobflowid");
dt.AcceptChanges();
//检查数据表是否为空，如果为空，则退出
if (dt == null)
{
return(false);
}
//创建Excel应用程序对象，如果未创建成功则退出
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
System.Web.HttpContext.Current.Response.Write("无法创建Excel对象，可能你的电脑未装Excel");
return(false);
}
//创建Excel的工作簿
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
range = (Excel.Range)worksheet.get_Range("A1", "I1");//获取表格第一行
range.Merge(0);
worksheet.Cells[1, 1] = this.biaoti.InnerText;
range.Font.Size = 22;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
//写入标题
for (int i = 0; i < dt.Columns.Count + 1; i++)
{
//写入标题名称
if (i == 0)
{
worksheet.Cells[2, i + 1] = "序号"; //加入序号列
}
else
{
worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];
}
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Interior.ColorIndex = 15;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (i == 0)//序号列宽度设为自动调整
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();
}
}
}
//写入DataTable中数据的内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count + 1; c++)
{
range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
//写入内容
if (c == 0) //增加序号
{
worksheet.Cells[r + 3, c + 1] = (r + 1).ToString();
}
else if (dt.Columns[c - 1].ColumnName == "happendate") //时间列
{
worksheet.Cells[r + 3, c + 1] = ((DateTime)dt.Rows[r][c - 1]).ToString("yyyy年MM月dd日");
}
else if (dt.Columns[c - 1].ColumnName == "ausmoney") //金额列
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
range.NumberFormat = "#,##0.00";
}
else if (dt.Columns[c - 1].ColumnName == "payStatus") //支付列
{
if (dt.Rows[r][c - 1].ToString() == "1")
{
worksheet.Cells[r + 3, c + 1] = "已支付";
}
else
{
worksheet.Cells[r + 3, c + 1] = "未支付";
}
}
else
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
}
//设置样式
range.Font.Size = 9;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
//设置单元格的宽度，如果小于8.5就设置为8.5，如果大于。则设置为自动
if (c == 0) //序号列宽度设为自动
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
//设置合计那一行
range = (Excel.Range)worksheet.get_Range("A" + (dt.Rows.Count + 3).ToString(), "I" + (dt.Rows.Count + 3).ToString());
range.Font.ColorIndex = 41;
//range.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Font.Size = 10;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
range.EntireColumn.AutoFit(); //自动调整列宽
worksheet.Cells[3 + dt.Rows.Count, 1] = "合计:"; //合计那一行的第一列
if (dt.Rows.Count == 0)
{
worksheet.Cells[3 + dt.Rows.Count, 7] = 0;
}
else
{
worksheet.Cells[3 + dt.Rows.Count, 7] = "=SUM(G3:G" + (dt.Rows.Count + 2).ToString() + ")";
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
System.Windows.Forms.Application.DoEvents();
try
{
workbook.Saved = true;
workbook.SaveCopyAs(AbosultedFilePath);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write("导出文件时出错，文件可能正被打开！\n" + ex.ToString());
return(false);
}
workbook.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killID = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killID = m;
}
}
if (excelProc[killID].HasExited == false)
{
excelProc[killID].Kill();
}
#endregion
return(true);
}
protected void ExportExcel(DataTable dt, string table)
{
if (dt == null || dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
return;
}
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
//Excel.Workbook workbook = xlApp.Workbooks.Open(table, 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 worksheet = workbook.Sheets[1] as Excel.Worksheet; //第一个sheet页
worksheet.Name = "武汉市公费"; //这里修改sheet名称
try
{
Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
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;
//range.NumberFormat = "0.00";
}
DataTable dtDec = DecCol();
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
string strValue = dt.Rows[r][i].ToString();
decimal dm = 0M;
if (dtDec.Columns.Contains(dt.Columns[i].ColumnName))
{
((Excel.Range)worksheet.Cells[r + 2, i + 1]).NumberFormat = "0.00";
worksheet.Cells[r + 2, i + 1] = strValue;
}
else
{
worksheet.Cells[r + 2, i + 1] = "'" + strValue;
//worksheet.Cells[r + 2, i + 1] = strValue;
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
xlApp.Visible = true;
}
catch (Exception ex)
{
}
finally
{
workbook.Saved = true;
if (System.IO.File.Exists(table))
{
System.IO.File.Delete(table);
}
workbook.SaveCopyAs(table);
workbook.Close(true, Type.Missing, Type.Missing);
workbook = null;
xlApp.Quit();
xlApp = null;
}
}
private void Save()
{
try
{
this.Cursor = PubStaticFun.WaitCursor();
#region 简单打印
DataTable tb = (DataTable)this.dataGridViewX1.DataSource;
//if (tb == null || tb.Rows.Count == 0)
//{
// return;
//}
Excel.Application myExcel = new Excel.ApplicationClass(); //new Excel.Application();
//关闭时不显示提示
//myExcel.DefaultFilePath = "";
//myExcel.DisplayAlerts = false;
//myExcel.SheetsInNewWorkbook = 1;
Excel.Workbook xlBook = myExcel.Application.Workbooks.Add(true);
//查询条件
string rq = "日期从:" + dtpjsrq1.Value.ToString() + " 到:" + dtpjsrq2.Value.ToString() + " 收费员：" + this.comboBox1.Text;
string ks = "";
string swhere = rq + ks;
//写入行头
int SumRowCount = tb.Rows.Count;
int SumColCount = 0;
for (int j = 0; j < this.dataGridViewX1.Columns.Count; j++)
{
if (this.dataGridViewX1.Columns[j].Visible)
{
SumColCount = SumColCount + 1;
myExcel.Cells[5, SumColCount] = "" + this.dataGridViewX1.Columns[j].HeaderText;
}
}
myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Bold = true;
myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5, SumColCount]).Font.Size = 10;
//逐行写入数据，
for (int i = 0; i < tb.Rows.Count; i++)
{
int ncol = 0;
for (int j = 0; j < tb.Columns.Count; j++)
{
if (this.dataGridViewX1.Columns[j].Visible)
{
ncol = ncol + 1;
myExcel.Cells[6 + i, ncol] = "'" + tb.Rows[i][j].ToString().Trim();
//if (tb.Columns[j].ColumnName.IndexOf("比例") >= 0)
//{
// myExcel.Cells[6 + i, ncol] = "'" + Convert.ToDecimal(Convert.ToDecimal(tb.Rows[i][j].ToString().Trim()) * 100).ToString("0.00") + "%";
//}
}
}
}
//设置报表表格为最适应宽度
myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Select(); //6
myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Columns.AutoFit(); //6
//加边框
myExcel.get_Range(myExcel.Cells[5, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Borders.LineStyle = 1;
//报表名称
string ss = TrasenFrame.Classes.Constant.HospitalName + "门诊预交金收支情况统计";
myExcel.Cells[1, 1] = ss;
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Bold = true;
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Font.Size = 16;
//报表名称跨行居中
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).Select();
myExcel.get_Range(myExcel.Cells[1, 1], myExcel.Cells[1, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//报表条件
myExcel.Cells[3, 1] = swhere.Trim();
myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Font.Size = 10;
myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[3, SumColCount]).Select();
myExcel.get_Range(myExcel.Cells[3, 1], myExcel.Cells[5, SumColCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//最后一行为黄色
myExcel.get_Range(myExcel.Cells[5 + SumRowCount, 1], myExcel.Cells[5 + SumRowCount, SumColCount]).Interior.ColorIndex = 19;
xlBook.SaveCopyAs(Application.StartupPath + "\\门诊预交金收支情况统计.xls");
//让Excel文件可见
myExcel.Visible = false;
//
//打印
//myExcel.Workbooks.Close();
if (xlBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
xlBook = null;
}
if (myExcel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myExcel = null;
//xlApp.Quit();
}
GC.Collect();
#endregion
}
catch (System.Exception err)
{
MessageBox.Show(err.Message + "错误提示");
}
finally
{
//this.btExcel.Enabled = true;
this.Cursor = Cursors.Arrow;
}
}
private void OutPut()
{
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
Excel.Range range;
string filename = "";
try
{
//生成.xls文件完整路径名
filename = Server.MapPath("/RailExamBao/Excel/CompuerServerCount.xls");
if (File.Exists(filename.ToString()))
{
File.Delete(filename.ToString());
}
//将所得到的表的列名,赋值给单元格
objSheet.Cells[1, 1] = "站段名称";
objSheet.Cells[1, 2] = "服务器名称";
range = objSheet.get_Range(objSheet.Cells[1, 2], objSheet.Cells[1, 4]);
range.Merge(0);
objSheet.Cells[1, 5] = "使用人次";
objSheet.Cells[1, 6] = "其他单位使用人次";
objSheet.Cells[1, 7] = "其他单位使用天数";
DataSet ds = (DataSet)ViewState["Grid"];
int i = 0;
//同样方法处理数据
foreach (DataRow dr in ds.Tables[0].Rows)
{
objSheet.Cells[2 + i, 1] = dr["Short_Name"].ToString();
objSheet.Cells[2 + i, 2] = dr["Computer_Server_Name"].ToString();
range = objSheet.get_Range(objSheet.Cells[2 + i, 2], objSheet.Cells[2 + i, 4]);
range.Merge(0);
objSheet.Cells[2 + i, 5] = dr["使用人次"].ToString();
objSheet.Cells[2 + i, 6] = dr["其他单位使用人次"].ToString();
objSheet.Cells[2 + i, 7] = dr["其他单位使用天数"].ToString();
i++;
}
//不可见,即后台处理
objApp.Visible = false;
objbook.Saved = true;
objbook.SaveCopyAs(filename);
}
catch
{
SessionSet.PageMessage = "系统错误，导出Excel文件失败！";
}
finally
{
objbook.Close(Type.Missing, filename, Type.Missing);
objbooks.Close();
objApp.Application.Workbooks.Close();
objApp.Application.Quit();
objApp.Quit();
GC.Collect();
}
}
private void OutPut()
{
RandomExamCountStatisticBLL objBll = new RandomExamCountStatisticBLL();
//I当前登录人ID
int _OrgId = PrjPub.CurrentLoginUser.StationOrgID;
//等1:路局 等于0:站段
int _SuitRangeId = PrjPub.CurrentLoginUser.SuitRange;
//考试开始时间
DateTime _DateFrom = Convert.ToDateTime(dateStartDateTime.DateValue);
//考试结束时间
DateTime _DateTo = Convert.ToDateTime(dateEndDateTime.DateValue);
int style = Convert.ToInt32(ddlStyle.SelectedValue);
IList <RailExam.Model.RandomExamCountStatistic> objList = objBll.GetCountWithOrg(_SuitRangeId, _OrgId, _DateFrom, _DateTo, PrjPub.GetRailSystemId(), style);
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
Excel.Range range;
string filename = "";
try
{
//生成.xls文件完整路径名
filename = Server.MapPath("/RailExamBao/Excel/Count.xls");
if (File.Exists(filename.ToString()))
{
File.Delete(filename.ToString());
}
//将所得到的表的列名,赋值给单元格
objSheet.Cells[1, 1] = "序号";
objSheet.Cells[1, 2] = "站段单位";
range = objSheet.get_Range(objSheet.Cells[1, 2], objSheet.Cells[1, 4]);
range.Merge(0);
objSheet.Cells[1, 5] = "考试次数";
objSheet.Cells[1, 6] = "参考人次";
//同样方法处理数据
for (int i = 0; i < objList.Count; i++)
{
objSheet.Cells[2 + i, 1] = i + 1;
objSheet.Cells[2 + i, 2] = objList[i].OrgName;
range = objSheet.get_Range(objSheet.Cells[2 + i, 2], objSheet.Cells[2 + i, 4]);
range.Merge(0);
objSheet.Cells[2 + i, 5] = objList[i].ExamCount;
objSheet.Cells[2 + i, 6] = objList[i].EmployeeCount;
}
//不可见,即后台处理
objApp.Visible = false;
objbook.Saved = true;
objbook.SaveCopyAs(filename);
}
catch
{
SessionSet.PageMessage = "系统错误，导出Excel文件失败！";
}
finally
{
objbook.Close(Type.Missing, filename, Type.Missing);
objbooks.Close();
objApp.Application.Workbooks.Close();
objApp.Application.Quit();
objApp.Quit();
GC.Collect();
}
}
private void ExportArrange()
{
// 根据 ProgressBar.htm 显示进度条界面
string templateFileName = Path.Combine(Server.MapPath("."), "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 strExamId = Request.QueryString.Get("id");
RandomExamArrangeBLL eaBll = new RandomExamArrangeBLL();
IList <RailExam.Model.RandomExamArrange> ExamArranges = eaBll.GetRandomExamArranges(int.Parse(strExamId));
RandomExamBLL objBll = new RandomExamBLL();
RailExam.Model.RandomExam objRandomExam = objBll.GetExam(Convert.ToInt32(Request.QueryString.Get("id")));
EmployeeBLL psBLL = new EmployeeBLL();
DataSet ds = new DataSet();
string[] str = ExamArranges[0].UserIds.Split(',');
IList <Employee> objList = new List <Employee>();
if (str[0] != "")
{
OracleAccess db = new OracleAccess();
string strSql;
OrganizationBLL orgBll = new OrganizationBLL();
for (int i = 0; i < str.Length; i++)
{
Employee obj = psBLL.GetChooseEmployeeInfo(str[i]);
obj.RowNum = i + 1;
if (PrjPub.CurrentLoginUser.RoleID != 1)
{
if (orgBll.GetStationOrgID(obj.OrgID) == PrjPub.CurrentLoginUser.StationOrgID)
{
objList.Add(obj);
}
}
else
{
objList.Add(obj);
}
}
if (objList.Count > 0)
{
ds.Tables.Add(ConvertToDataTable((IList)objList));
if (ViewState["Sort"] != null)
{
ds.Tables[0].DefaultView.Sort = ViewState["Sort"].ToString();
}
DataColumn dc = ds.Tables[0].Columns.Add("ComputeRoom");
strSql = "select a.*,c.Short_Name||'-'||b.Computer_Room_Name as ComputeRoom "
+ " from Random_Exam_Arrange_Detail a "
+ " inner join Computer_Room b on a.Computer_Room_ID=b.Computer_Room_ID"
+ " inner join Org c on b.Org_ID=c.Org_ID"
+ " where Random_Exam_ID='" + strExamId + "'";
DataSet dsDetail = db.RunSqlDataSet(strSql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
string strUser = "******" + dr["EmployeeID"] + ",";
DataRow[] drs = dsDetail.Tables[0].Select("','+User_Ids+',' like '%" + strUser + "%'");
if (drs.Length > 0)
{
dr["ComputeRoom"] = drs[0]["ComputeRoom"].ToString();
}
else
{
dr["ComputeRoom"] = string.Empty;
}
}
}
System.Threading.Thread.Sleep(10);
string jsBlock = "<script>SetPorgressBar('导出考生信息','" + ((1 * 100) / ((double)(ds.Tables[0].Rows.Count + 1))).ToString("0.00") + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
#region OWC11
/*
* SpreadsheetClass xlsheet = new SpreadsheetClass();
* Worksheet ws = (Worksheet)xlsheet.Worksheets[1];
* ws.Cells.Font.set_Size(10);
* ws.Cells.Font.set_Name("宋体");
*
* ws.Cells[1, 1] = objRandomExam.ExamName + " 参加考试学员名单";
* Range range = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 7]);
* range.set_MergeCells(true);
* range.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
* range.Font.set_Name("宋体");
*
*
* //write headertext
* ws.Cells[2, 1] = "序号";
* ((Range)ws.Cells[2, 1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
*
*
* ws.Cells[2, 2] = "姓名";
* ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_MergeCells(true);
* ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
*
* if (PrjPub.IsWuhan())
* {
* ws.Cells[2, 3] = "员工编码";
* }
* else
* {
* ws.Cells[2, 3] = "工资编号";
* }
* ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_MergeCells(true);
* ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
*
* ws.Cells[2, 4] = "职名";
* ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_MergeCells(true);
* ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
*
* ws.Cells[2, 5] = "组织机构";
* ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_MergeCells(true);
* ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
*
* ws.Cells[2, 8] = "考试地点";
* ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 10]).set_MergeCells(true);
* ws.get_Range(ws.Cells[2, 8], ws.Cells[2, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
*
* int j = 0;
* foreach(DataRow dr in ds.Tables[0].Rows)
* {
* ws.Cells[3 + j, 1] = j + 1;
*
* ws.Cells[3 + j, 2] = dr["EmployeeName"].ToString();
* ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_MergeCells(true);
* ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
*
* ws.Cells[3 + j, 3] = "'" + dr["StrWorkNo"].ToString();
* ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_MergeCells(true);
* ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
*
*
* ws.Cells[3 + j, 4] = dr["PostName"].ToString();
* ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_MergeCells(true);
* ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
*
* ws.Cells[3 + j, 5] = dr["OrgName"].ToString();
* ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_MergeCells(true);
* ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
*
* ws.Cells[3 + j, 8] = dr["ComputeRoom"].ToString();
* ws.get_Range(ws.Cells[3 + j, 8], ws.Cells[3 + j, 10]).set_MergeCells(true);
* ws.get_Range(ws.Cells[3 + j, 8], ws.Cells[3 + j, 10]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
*
* j++;
*
* System.Threading.Thread.Sleep(10);
* jsBlock = "<script>SetPorgressBar('导出考生信息','" + (((j + 1) * 100) / ((double)(ds.Tables[0].Rows.Count + 1))).ToString("0.00") + "'); </script>";
* Response.Write(jsBlock);
* Response.Flush();
* }
*
* ws.Name = "1-1";
* ws.Cells.Columns.AutoFit();
*
*
* ((Worksheet)xlsheet.Worksheets[1]).Activate();
*
* string path = Server.MapPath("../Excel/Excel.xls");
* if (File.Exists(path))
* File.Delete(path);
* xlsheet.Export(path, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportAsAppropriate);
*/
#endregion
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
Excel.Range rang1;
string filename = "";
try
{
//生成.xls文件完整路径名
filename = Server.MapPath("/RailExamBao/Excel/Excel.xls");
if (File.Exists(filename.ToString()))
{
File.Delete(filename.ToString());
}
objSheet.Cells.Font.Size = 10;
objSheet.Cells.Font.Name = "宋体";
objSheet.Cells[1, 1] = objRandomExam.ExamName + " 参加考试学员名单";
rang1 = objSheet.get_Range(objSheet.Cells[1, 1], objSheet.Cells[1, 6]);
rang1.Merge(0);
rang1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
rang1.Font.Bold = true;
objSheet.Cells.Font.Size = 17;
objSheet.Cells.Font.Name = "宋体";
objSheet.Cells.Font.Size = 12;
objSheet.Cells.Font.Name = "宋体";
//write headertext
objSheet.Cells[2, 1] = "序号";
((Excel.Range)objSheet.Cells[2, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[2, 2] = "姓名";
objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).Merge(0);
objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[2, 3] = "员工编码";
objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).Merge(0);
objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[2, 4] = "职名";
objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).Merge(0);
objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[2, 5] = "组织机构（车间）";
objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).Merge(0);
objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[2, 6] = "考试地点";
objSheet.get_Range(objSheet.Cells[2, 6], objSheet.Cells[2, 6]).Merge(0);
objSheet.get_Range(objSheet.Cells[2, 6], objSheet.Cells[2, 6]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
int j = 0;
foreach (DataRow dr in ds.Tables[0].Rows)
{
objSheet.Cells[3 + j, 1] = j + 1;
objSheet.Cells[3 + j, 2] = dr["EmployeeName"].ToString();
objSheet.get_Range(objSheet.Cells[6 + j, 2], objSheet.Cells[6 + j, 2]).Merge(0);
objSheet.get_Range(objSheet.Cells[6 + j, 2], objSheet.Cells[6 + j, 2]).HorizontalAlignment =
XlHAlign.xlHAlignLeft;
objSheet.Cells[3 + j, 3] = "'" + dr["StrWorkNo"].ToString();
objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).Merge(0);
objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).HorizontalAlignment =
XlHAlign.xlHAlignLeft;
objSheet.Cells[3 + j, 4] = dr["PostName"].ToString();
objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).Merge(0);
objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
objSheet.Cells[3 + j, 5] = dr["OrgName"].ToString();
objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).Merge(0);
objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
objSheet.Cells[3 + j, 6] = dr["ComputeRoom"].ToString();
objSheet.get_Range(objSheet.Cells[3 + j, 6], objSheet.Cells[3 + j, 6]).Merge(0);
objSheet.get_Range(objSheet.Cells[3 + j, 6], objSheet.Cells[3 + j, 6]).HorizontalAlignment =
XlHAlign.xlHAlignLeft;
j++;
System.Threading.Thread.Sleep(10);
jsBlock = "<script>SetPorgressBar('导出考生信息','" + (((j + 1) * 100) / ((double)(ds.Tables[0].Rows.Count + 1))).ToString("0.00") + "'); </script>";
Response.Write(jsBlock);
Response.Flush();
}
objSheet.Cells.Columns.AutoFit();
objApp.Visible = false;
objbook.Saved = true;
objbook.SaveCopyAs(filename);
}
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='true';window.close();</script>");
}
protected void btnOutPut_Click(object sender, EventArgs e)
{
try
{
RandomExamBLL objBll = new RandomExamBLL();
RailExam.Model.RandomExam objRandomExam = objBll.GetExam(Convert.ToInt32(Request.QueryString.Get("eid")));
IList <Employee> objList = BindGrid();
#region 原导出Excel方法
//SpreadsheetClass xlsheet = new SpreadsheetClass();
//Worksheet ws = (Worksheet)xlsheet.Worksheets[1];
//ws.Cells.Font.set_Size(10);
//ws.Cells.Font.set_Name("宋体");
//ws.Cells[1, 1] = objRandomExam.ExamName + " 未参加考试学员名单";
//Range rang1 = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 7]);
//rang1.set_MergeCells(true);
//rang1.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
//rang1.Font.set_Name("宋体");
////write headertext
//ws.Cells[2, 1] = "序号";
//((Range)ws.Cells[2, 1]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
//ws.Cells[2, 2] = "姓名";
//ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_MergeCells(true);
//ws.get_Range(ws.Cells[2, 2], ws.Cells[2, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
//if (PrjPub.IsWuhan())
//{
// ws.Cells[2, 3] = "员工编码(身份证号码)";
//}
//else
//{
// ws.Cells[2, 3] = "工资编号";
//}
//ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_MergeCells(true);
//ws.get_Range(ws.Cells[2, 3], ws.Cells[2, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
//ws.Cells[2, 4] = "职名";
//ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_MergeCells(true);
//ws.get_Range(ws.Cells[2, 4], ws.Cells[2, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
//ws.Cells[2, 5] = "组织机构";
//ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_MergeCells(true);
//ws.get_Range(ws.Cells[2, 5], ws.Cells[2, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
//for (int j = 0; j < objList.Count; j++)
//{
// ws.Cells[3 + j, 1] = j + 1;
// ws.Cells[3 + j, 2] = objList[j].EmployeeName;
// ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_MergeCells(true);
// ws.get_Range(ws.Cells[3 + j, 2], ws.Cells[3 + j, 2]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
// ws.Cells[3 + j, 3] = "'" + objList[j].StrWorkNo;
// ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_MergeCells(true);
// ws.get_Range(ws.Cells[3 + j, 3], ws.Cells[3 + j, 3]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
// ws.Cells[3 + j, 4] = objList[j].PostName;
// ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_MergeCells(true);
// ws.get_Range(ws.Cells[3 + j, 4], ws.Cells[3 + j, 4]).set_HorizontalAlignment(XlHAlign.xlHAlignLeft);
// ws.Cells[3 + j, 5] = objList[j].OrgName;
// ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_MergeCells(true);
// ws.get_Range(ws.Cells[3 + j, 5], ws.Cells[3 + j, 7]).set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
//}
//ws.Name = "1-1";
//ws.Cells.Columns.AutoFit();
//((Worksheet)xlsheet.Worksheets[1]).Activate();
//string path = Server.MapPath("../Excel/Excel.xls");
//if (File.Exists(path))
// File.Delete(path);
//xlsheet.Export(path, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportAsAppropriate);
#endregion
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
Excel.Range rang1;
string filename = "";
//生成.xls文件完整路径名
filename = Server.MapPath("/RailExamBao/Excel/Excel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
if (File.Exists(filename.ToString()))
{
File.Delete(filename.ToString());
}
objSheet.Cells.Font.Size = 10;
objSheet.Cells.Font.Name = "宋体";
objSheet.Cells[1, 1] = objRandomExam.ExamName + " 未参加考试学员名单";
rang1 = objSheet.get_Range(objSheet.Cells[1, 1], objSheet.Cells[1, 7]);
rang1.Merge(0);
rang1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
rang1.Font.Bold = true;
objSheet.Cells.Font.Size = 17;
objSheet.Cells.Font.Name = "宋体";
//write headertext
objSheet.Cells[2, 1] = "序号";
((Excel.Range)objSheet.Cells[2, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[2, 2] = "姓名";
objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).Merge(0);
objSheet.get_Range(objSheet.Cells[2, 2], objSheet.Cells[2, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[2, 3] = "员工编码(身份证号码)";
objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).Merge(0);
objSheet.get_Range(objSheet.Cells[2, 3], objSheet.Cells[2, 3]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[2, 4] = "职名";
objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).Merge(0);
objSheet.get_Range(objSheet.Cells[2, 4], objSheet.Cells[2, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[2, 5] = "组织机构";
objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).Merge(0);
objSheet.get_Range(objSheet.Cells[2, 5], objSheet.Cells[2, 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
for (int j = 0; j < objList.Count; j++)
{
objSheet.Cells[3 + j, 1] = j + 1;
objSheet.Cells[3 + j, 2] = objList[j].EmployeeName;
objSheet.get_Range(objSheet.Cells[3 + j, 2], objSheet.Cells[3 + j, 2]).Merge(0);
objSheet.get_Range(objSheet.Cells[3 + j, 2], objSheet.Cells[3 + j, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[3 + j, 3] = "'" + objList[j].StrWorkNo;
objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).Merge(0);
objSheet.get_Range(objSheet.Cells[3 + j, 3], objSheet.Cells[3 + j, 3]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[3 + j, 4] = objList[j].PostName;
objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).Merge(0);
objSheet.get_Range(objSheet.Cells[3 + j, 4], objSheet.Cells[3 + j, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
objSheet.Cells[3 + j, 5] = objList[j].OrgName;
objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).Merge(0);
objSheet.get_Range(objSheet.Cells[3 + j, 5], objSheet.Cells[3 + j, 5]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
objSheet.Columns.AutoFit();
objApp.Visible = false;
objbook.Saved = true;
objbook.SaveCopyAs(filename);
FileInfo file = new FileInfo(filename);
this.Response.Clear();
this.Response.Buffer = true;
this.Response.Charset = "utf-7";
this.Response.ContentEncoding = Encoding.UTF7;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(objRandomExam.ExamName + "未参加考试学员名单") + ".xls");
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
this.Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
this.Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
this.Response.WriteFile(file.FullName);
}
catch
{
SessionSet.PageMessage = "系统错误，导出Excel文件失败！";
}
}
public void ExportToExcel(System.Data.DataTable dt)
{
if (dt == null)
{
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
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
//写入标题
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; //粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
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];
range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9;//字体大小
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs("C:\\datatable" + "" + ".xls");
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
xlApp.Quit();
GC.Collect();//强行销毁
//这个是从服务器中下载文件,(请参考我另外一个文章)
//参考网址http://www.cnblogs.com/ghostljj/archive/2007/01/24/629293.html
//BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response, "ReportToExcel.xls"
// , System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name + ".xls", 1024000);
}
/// <summary>
/// 将datatable导出excel文件
/// </summary>
/// <param name="dt">需要导出的datatable</param>
/// <param name="AbosultedFilePath">导出文件的绝对路径</param>
/// <returns></returns>
public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
//检查数据表是否为空，如果为空，则退出
if (dt == null)
{
return(false);
}
//创建Excel应用程序对象，如果未创建成功则退出
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
System.Web.HttpContext.Current.Response.Write("无法创建Excel对象，可能你的电脑未装Excel");
return(false);
}
//创建Excel的工作簿
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
range = (Excel.Range)worksheet.get_Range("A1", "O1"); //获取表格中第一行
range.Merge(0); //合并第一行
worksheet.Cells[1, 1] = this.selectyear.SelectedValue + "年度报销费用-部门报销费用按月度汇总"; //大标题
range.Font.Size = 22;
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
//写入标题
for (int i = 0; i < dt.Columns.Count + 1; i++)
{
//写入标题名称
if (i == 0)
{
worksheet.Cells[2, i + 1] = "序号";
}
else
{
worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];//从第二行的第一格开始写数据
}
//设置标题的样式
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
range.Interior.ColorIndex = 15;
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色
if (i == 0) //序号列宽度自动
{
range.EntireColumn.AutoFit();
}
else
{
//设置单元格的宽度，如果小于9就设置为9，如果大于。则设置为自动
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
//写入DataTable中数据的内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count + 1; c++)
{
range = (Excel.Range)worksheet.Cells[r + 3, c + 1];
//写入内容
if (c == 0)
{
if (r == dt.Rows.Count - 1)
{
worksheet.Cells[r + 3, c + 1] = "";
}
else
{
worksheet.Cells[r + 3, c + 1] = (r + 1).ToString(); //得到序号
}
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
}
else if (c == 1)
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString();
}
else
{
worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString().Split(',')[0];
range.NumberFormat = "#,##0.00";
}
//设置样式
range.Font.Size = 9; //字体大小
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
//设置单元格的宽度，如果小于8.5就设置为8.5，如果大于。则设置为自动
if (c == 0) //序号列宽度设为自动
{
range.EntireColumn.AutoFit();
}
else
{
if (range.EntireColumn.ColumnWidth <= 8.5)
{
range.EntireColumn.ColumnWidth = 8.5;
}
else
{
range.EntireColumn.AutoFit();//自动设置列宽
}
}
}
if (r == dt.Rows.Count - 1)
{
range = (Excel.Range)worksheet.get_Range("A" + (r + 3).ToString(), "B" + (r + 3).ToString());
range.Merge(0);
range.Value = "合计";
range.Font.Size = 9;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
range.EntireColumn.AutoFit();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(AbosultedFilePath);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write("导出文件时出错，文件可能正被打开！\n" + ex.ToString());
return(false);
}
workbook.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killID = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killID = m;
}
}
if (excelProc[killID].HasExited == false)
{
excelProc[killID].Kill();
}
#endregion
return(true);
}
