.net操作excel
.NET操作EXCEL
//页面初始化
//获取 _page并赋值
public void Initial(SlnSuwfPage page)
{
this._page = page;
this.BindButtonEvent("btnExport2");//给导出明细按钮添加事件
}
#region 绑定按钮事件
/// <summary>
/// 绑定按钮事件
/// </summary>
/// <param name="btnName"></param>
private void BindButtonEvent(string btnName)
{
//导出明细按钮
Control ctrl = this._page.FindControl(btnName);
if (ctrl == null) return;
if (btnName == "btnExport2")
{
Button btnExportEXCEL = (Button)ctrl;
if (btnExportEXCEL == null)
return;
btnExportEXCEL.Click += new EventHandler(btnExportEXCEL_Click);
}
}
#endregion
#region 注册按钮事件
/// <summary>
/// 注册按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void btnExportEXCEL_Click(object sender, EventArgs e)
{
int count = _page.GridData.Rows.Count;
string pk_value = _page.GetRowDataPrimaryKeys();
if (string.IsNullOrEmpty(pk_value))
{
_page.ShowMessage("请选择要导出的数据！");
return;
}
string[] str = pk_value.Split(',');
//调换字符数组的顺序，使导出的数据和页面显示的数据一样
//int Begin = 0;
//int End = str.Length - 1;
//while (End >= Begin)
//{
// string t = str[Begin];
// str[Begin] = str[End];
// str[End] = t;
// Begin++;
// End--;
//}
//选择一条数据时-----应急领料单
#region
if (str.Length == 1)
{
strFileName = "应急领料单.xls";
string strSql = @"select t.*,decode (ZT,2,'未出帐',3,'未补单',4,'已补单',5,'缺组件',6,'未下达',7,'已出账',1) ZT2 "
+ "from (select YJLL_BM,SYDW,SGDW,ZZMC,LLYT,LLFZR,TEL,decode(GET_GETSFTD_FC(yjll_bm), 0, 3, 4) ZT,"
+ "U_REGISTIME,BZ "
+ "from WZGL_NEW_YJLL_TB "
+ "where YJLL_BM='" + str[0] + "') t";
string strSql2 = @"select WZ_ZSHBZM as WZ_BM,WZ_MC,JLDW,SLSL,ERP_DH,BZ "
+ "from WZGL_NEW_YJLLWZ_TB "
+ "where wz_bm='" + str[0] + "'";
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("dt", strSql);
dic.Add("dt1", strSql2);
DataSet ds = new DataSet();
foreach (KeyValuePair<string, string> k in dic)
{
SlnDataAccess.FillDataSet(ds, k.Key, k.Value);
}
string strMsg = Salien.Utility.SUWF.ClsPrintExcel.ExportExcel("", ds, strFileName, false, "HTML");
if (strMsg == "")
{
_page.ShowMessage("导出成功！");
}
else
{
_page.ShowMessage("导出失败！" + strMsg);
}
}
#endregion
//选择多条数据时-----应急领料单2
#region
else
{
//for (int i = 0; i < str.Length; i++)
//{
strFileName = "应急领料单2";
//string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "REPORT\\Templates\\" + FileName;
string fileFullName = AppDomain.CurrentDomain.BaseDirectory
+ "REPORT\\Generates\\" + strFileName + System.Guid.NewGuid().ToString() + ".xls";
string templateFilePath = strFilePath + strFileName + ".xls";
//string YJLL_BM = str[i].ToString();
string strSql = "select ''''||YJLL_BM as YJLL_BM ,"
+ " SYDW,"
+ " ''''||WZ_ZSHBZM as WZ_BM,"
+ " ''''||WZ_MC as WZ_MC,"
+ "JLDW,"
+ " SLSL,"
+ "'' as slsl,"
+ " ERP_DH, "
+ " t.BZ as BZ1,"
+ " SGDW,"
+ "ZZMC,"
+ "LLYT,"
+ " LLFZR, "
+ " ''''||TEL as TEL ,"
+ "decode(ZT,2, '未出帐', 3, '未补单', 4, '已补单', 5, '缺组件', 6,"
+ " '未下达', 7, '已出账', 1) ZT2,"
+ " U_REGISTIME ,"
+ " w.BZ"
+ " from"
+ " (select YJLL_BM,SYDW,SGDW,ZZMC,LLYT,LLFZR, TEL,"
+ " decode(GET_GETSFTD_FC(yjll_bm), 0, 3, 4) ZT,"
+ " U_REGISTIME, BZ from WZGL_NEW_YJLL_TB "
+ " where YJLL_BM in(" + pk_value + ")) t,WZGL_NEW_YJLLWZ_TB w "
+ " where wz_bm(+)=YJLL_BM order by YJLL_BM desc";
DataTable dt = SlnDataAccess.GetDataTable(strSql);
string str_Msg = OperatExcel(dt, templateFilePath, fileFullName, 4);
//下载时要显示的文件名
string dstrSql = "select to_char(sysdate,'yyyy-mm-dd') from dual";
string downloadName = SlnDataAccess.GetValueBySql(dstrSql).ToString() + ".xls";
if (str_Msg == "")
{
//下载文件方式
FileStream fileStream = new FileStream(fileFullName, FileMode.Open, FileAccess.Read, FileShare.Read);
int length = (int)fileStream.Length;
byte[] filebyte = new byte[length];
fileStream.Read(filebyte, 0, length);
fileStream.Close();
_page.Response.Clear();
_page.Response.Charset = "GB2312";
_page.Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
_page.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(downloadName)); // 添加头信息，指定文件大小，让浏览器能够显示下载进度
_page.Response.AddHeader("Content-Length", length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
_page.Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
_page.Response.BinaryWrite(filebyte);
_page.Response.Flush();
// 停止页面的执行
_page.Response.End();
}
else
{
_page.ShowMessage("导出失败！" + str_Msg);
}
//}
}
#endregion
}
#endregion
#region 操作Excel
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <param name="strFileName"></param>
public string OperatExcel(DataTable _dt, string templateFilePath, string fileFullName, int startRowIndex)
{
string strMsg = "";
//startRowIndex = startRowIndex + 1;
try
{
Microsoft.Office.Interop.Excel.Application app = null; // Excel 对象
Microsoft.Office.Interop.Excel.Workbook workbook = null; // Workbook 对象
Microsoft.Office.Interop.Excel.Worksheet sheet = null; // Worksheet 对象
Microsoft.Office.Interop.Excel.Range range = null; // Range 对象
app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
app.DisplayAlerts = false;
// 获取Workbook
workbook = (Microsoft.Office.Interop.Excel.Workbook)(app.Workbooks.Add(templateFilePath));
sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
object[,] rangeValue;
// string strValue = "";
int rowIndex = startRowIndex;
////设置时间
//sheet.Cells[5, 1] = strExcelTime;
int colIndex = 0;
#region 填充内容
range = sheet.get_Range(sheet.Cells[startRowIndex, 1], sheet.Cells[startRowIndex + _dt.Rows.Count - 1, _dt.Columns.Count]); rangeValue = new object[_dt.Rows.Count, _dt.Columns.Count];
for (int i = 0; i < _dt.Rows.Count; i++)
{
for (int j = 1; j < _dt.Columns.Count; j++)
{
colIndex = j - 1;
rangeValue[i, colIndex] = _dt.Rows[i][j - 1];
}
}
range.set_Value(Type.Missing, rangeValue);
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
range.WrapText = true;//自动换行
range.EntireRow.AutoFit();//行高自动调整
#endregion
#region 合并列
List<string> columnNameLst = new List<string>();//按列相同内容合并
columnNameLst.Add("YJLL_BM");//列名
List<string> columnNameLst1 = new List<string>();//根据YJLL_BM确定合并
columnNameLst1.Add("SYDW");
columnNameLst1.Add("SGDW");//罐量
columnNameLst1.Add("ZZMC");
columnNameLst1.Add("LLYT");
columnNameLst1.Add("LLFZR");
columnNameLst1.Add("TEL");
columnNameLst1.Add("ZT2");
columnNameLst1.Add("U_REGISTIME");
columnNameLst1.Add("BZ");
string ccName = "";
for (int c = 0; c < _dt.Columns.Count; c++)
{
colIndex = c + 1;
ccName = _dt.Columns[c].ColumnName;
if (!columnNameLst.Contains(ccName) && !columnNameLst1.Contains(ccName))
{
continue;
}
for (int r = startRowIndex; r < _dt.Rows.Count + startRowIndex - 1; r++)
{ if (columnNameLst.Contains(ccName))
{
string text1 = _dt.Rows[r - startRowIndex][c].ToString();
string text2 = _dt.Rows[r - startRowIndex + 1][c].ToString();
if (text1 == text2)
{
//sheet.get_Range(sheet.Cells[r, colIndex], sheet.Cells[r + 1, colIndex]).Merge(0); Microsoft.Office.Interop.Excel.Range cc;
cc = sheet.get_Range(sheet.Cells[r, colIndex], sheet.Cells[r + 1, colIndex]);
cc.MergeCells = true;
}
}
else if (columnNameLst1.Contains(ccName))
{
string text1 = _dt.Rows[r - startRowIndex][c].ToString();
string text2 = _dt.Rows[r - startRowIndex + 1][c].ToString();
string text3 = _dt.Rows[r - startRowIndex][0].ToString();
string text4 = _dt.Rows[r - startRowIndex + 1][0].ToString();
if (text1 == text2 && text3 == text4)
{
sheet.get_Range(sheet.Cells[r, colIndex], sheet.Cells[r + 1, colIndex]).Merge(0);
}
}
}
}
#endregion
#region 保存
//workbook.SaveAs(strFilePath + strFileName + ".xls",
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing);
workbook.SaveAs(fileFullName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, false, false, null, null);
#endregion
sheet = null;
workbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
workbook = null;
app.Quit();
app = null;
GC.Collect();
return strMsg;
}
catch (Exception e)
{
throw e;
}
}
#endregion
