using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
//using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using Infragistics.WebUI.UltraWebGrid;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Diagnostics;
namespace Supcon.MES.PetroSuite.Web.Suite.Utility
{
public class ExportToExcel_compine
{
//private Microsoft.Office.Interop.Excel.Application xlApp ;
//private Microsoft.Office.Interop.Excel.Workbook workbook ;
//2010-03-30修改
private Microsoft.Office.Interop.Excel.Application xlApp = new Application();
private _Workbook _workBook = null;
private Worksheet _workSheet = null;
private object missing = System.Reflection.Missing.Value;
private byte[] Buffer;
//导出文件的路径（长名）
private string Report = "";
//导入到Excel时的行开始位置
private int rowStartIndex = 1;
//导入到Excel时的列开始位置
private int colStartIndex = 1;
//是否显示标题
bool isShowTitle = true;
//是否显示边框线
bool isShowGridLine = true;
//表格标题字体大小
private int titleFontSize = 14;
//表格内容字体大小
private int tableFontSize = 12;
string newLine = "<br />";
string connect = "_";
/**/
/// <summary>
/// 获取或设置导入到Excel时在Excel中行的开始位置(大于0的整数)
/// </summary>
public int RowStartIndex
{
get { return rowStartIndex; }
set
{
if (value > 0)
{
rowStartIndex = value;
}
else
{
rowStartIndex = 1;
}
}
}
/**/
/// <summary>
/// 获取或设置导入到Excel时在Excel中列的开始位置(大于0的整数)
/// </summary>
public int ColStartIndex
{
get { return colStartIndex; }
set
{
if (value > 0)
{
colStartIndex = value;
}
else
{
colStartIndex = 1;
}
}
}
/**/
/// <summary>
/// 获取或设置是否显示表格标题
/// </summary>
public bool IsShowTitle
{
get { return isShowTitle; }
set { isShowTitle = value; }
}
/**/
/// <summary>
/// 获取或设置是否显示表格的边框和格线
/// </summary>
public bool IsShowGridLine
{
get { return isShowGridLine; }
set { isShowGridLine = value; }
}
/**/
/// <summary>
/// 获取或设置表格标题字体大小(大于0的整数)
/// </summary>
public int TitleFontSize
{
get { return titleFontSize; }
set
{
if (value > 0)
{
titleFontSize = value;
}
else
{
titleFontSize = 14;
}
}
}
/**/
/// <summary>
/// 获取或设置表格内容字体大小(大于0的整数)
/// </summary>
public int TableFontSize
{
get { return tableFontSize; }
set
{
if (value > 0)
{
tableFontSize = value;
}
else
{
tableFontSize = 12;
}
}
}
/**/
/// <summary>
/// 构造函数
/// </summary>
/// <param name="tempDirectory">存放临时文件的目录</param>
public ExportToExcel_compine(string tempDirectory)
{
try
{
this.xlApp.DisplayAlerts = false;
_workBook = xlApp.Workbooks.Add(XlSheetType.xlWorksheet);
_workSheet = (Worksheet)_workBook.ActiveSheet;
_workSheet.Name = "workSheetName";
this.Report = this.Report = System.IO.Path.Combine(tempDirectory, DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xls");
}
catch
{
CloseExcle();
}
}
/// <summary>
/// 析构函数
/// </summary>
~ExportToExcel_compine()
{
CloseExcle();
}
/**/
/// <summary>
/// 将UltraWebGrid中的内容导入到Excel文档中
/// </summary>
/// <param name="grid">导出数据的UltraWebGrid的ID</param>
/// <param name="title">导出的表格的标题</param>
/// <param name="isShowHiddenRow">标志是否显示隐藏的行</param>
/// <param name="isShowHiddenCol">标志是否显示隐藏的列</param>
/// <param name="Response">封装来自ASP.NET操作的HTTP相应信息</param>
public void UltraWebGridExportToExcel1(IList<UltraWebGrid> grids, string title, bool isShowHiddenRow, bool isShowHiddenCol, HttpResponse Response,int [] num,string [] xmlColumn)
{
//注意：Excel的行列序均从1开始
try
{
//记录当前画到哪行
int rowIndex = rowStartIndex;
//记录当前画到哪列
int colIndex = colStartIndex;
int count = 0;
foreach (UltraWebGrid grid in grids)
{
Infragistics.WebUI.UltraWebGrid.HeadersCollection dt = grid.Bands[0].HeaderLayout;
#region 画表前的预处理
//清除掉被覆盖的表头
for (int i = 0; i < dt.Count; i++)
{
if (!dt[i].HasRowLayoutColumnInfo)
{
dt.RemoveAt(i);
i--;
}
}
//如果不显示隐藏列的话，先删除隐藏的列对应的表头,然后清除隐藏列
List<int> list = new List<int>();//用来记录那些列是被隐藏了
if (!isShowHiddenCol)
{
for (int i = 0; i < grid.Columns.Count; i++)
{
if (grid.Columns[i].Hidden)
{
list.Add(i);
//删除表头
for (int j = 0; j < dt.Count; j++)
{
if (dt[j].RowLayoutColumnInfo.OriginX == i)
{
dt.RemoveAt(j);
j--;
}
}
}
}
把删除的列的后一列的表头往前串
int m = dt.Count;
for (int i = 0; i < list.Count; i++)
{
for (int j = 0; j < m; j++)
{
if (dt[j].RowLayoutColumnInfo.OriginX - 1 >= list[i])
{
dt[j].RowLayoutColumnInfo.OriginX = j;
}
}
m--;
}
删除列
for (int i = 0; i < grid.Columns.Count; i++)
{
if (grid.Columns[i].Hidden)
{
grid.Columns.RemoveAt(i);
i--;
}
}
}
#endregion
#region 画表过程
//画表格标题
if (isShowTitle)
{
Microsoft.Office.Interop.Excel.Range rangeTitle = xlApp.get_Range(xlApp.Cells[rowStartIndex, colStartIndex], xlApp.Cells[rowStartIndex, colStartIndex + grid.Columns.Count - 1]);
rangeTitle.MergeCells = true;
rangeTitle.Font.Size = titleFontSize;
rangeTitle.Font.Bold = true;
xlApp.Cells[rowStartIndex, colStartIndex] = title;
rowIndex++;
}
//开始画表头
for (int i = 0; i < dt.Count; i++)
{
string text = dt[i].Caption.ToString();
//在webgrid中的坐标
int x1 = dt[i].RowLayoutColumnInfo.OriginX;
int y1 = dt[i].RowLayoutColumnInfo.OriginY;
int x2 = x1 + dt[i].RowLayoutColumnInfo.SpanX;
int y2 = y1 + dt[i].RowLayoutColumnInfo.SpanY;
//在excel中的坐标
int cellx1;
int cellx2;
if (isShowTitle)
{
cellx1 = y1 + 1 + rowStartIndex + 1 - 1;
cellx2 = y2 + rowStartIndex + 1 - 1;
}
else
{
cellx1 = y1 + 1 + rowStartIndex - 1;
cellx2 = y2 + rowStartIndex - 1;
}
int celly1 = x1 + 1 + colStartIndex - 1;
int celly2 = x2 + colStartIndex - 1;
Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[cellx1, celly1], xlApp.Cells[cellx2, celly2]);
range.MergeCells = true;
range.Font.Size = tableFontSize;
range.Font.Bold = true;
//表格线
if (isShowGridLine)
{
range.Borders.LineStyle = 1;
}
xlApp.Cells[cellx1, celly1] = text;
//修改标志
if (rowIndex < cellx1)
{
rowIndex = cellx1;
}
if (rowIndex < cellx2)
{
rowIndex = cellx2;
}
}
//画数据
for (int i = 0; i < grid.Rows.Count; i++)
{
if (!isShowHiddenRow && grid.Rows[i].Hidden)
{
continue;
}
rowIndex++;
colIndex = colStartIndex;
for (int j = 0; j < grid.Columns.Count; j++)
{
xlApp.get_Range(xlApp.Cells[rowIndex, colIndex], xlApp.Cells[rowIndex, colIndex]).NumberFormatLocal = "@";
if (string.IsNullOrEmpty(grid.Rows[i].Cells[j].Text) == false)
{
xlApp.Cells[rowIndex, colIndex] = grid.Rows[i].Cells[j].Text.Replace(newLine, connect);
}
else
{
xlApp.Cells[rowIndex, colIndex] = grid.Rows[i].Cells[j].Text;
}
//表格线
if (isShowGridLine)
{
xlApp.get_Range(xlApp.Cells[rowIndex, colIndex], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
}
xlApp.get_Range(xlApp.Cells[rowIndex, colIndex], xlApp.Cells[rowIndex, colIndex]).Font.Size = tableFontSize;
colIndex++;
}
}
#endregion
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
GroupRows(_workSheet, xmlColumn [0], num[0]);
GroupRows(_workSheet, xmlColumn[1], num[1]);
GroupRows(_workSheet, xmlColumn[2], num[2]);
//保存临时文件到服务器端
_workBook.SaveAs(this.Report, XlFileFormat.xlTemplate, missing, missing, missing, missing, XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
count++;
}
//发送文件到客户端
SendFileToClient(Response);
}
catch (Exception ex)
{
CloseExcle();
//throw (ex);
}
}
public static void GroupRows(Worksheet _workSheet, string colum, int num)
{
int i = 3, rowSpanNum = 1;
while (i <= (num + 2))
{
string coordinate = colum + i.ToString();
string s1 = _workSheet.get_Range(coordinate, coordinate).Value2.ToString();
for (++i; i <= (num + 2); i++)
{
string coordinate2 = colum + i.ToString();
string s2 = _workSheet.get_Range(coordinate2, coordinate2).Value2.ToString();
if (s1.CompareTo(s2) == 0)
{
rowSpanNum++;
}
else
{
string coordinate_start = colum + (i - rowSpanNum).ToString();
string coordinate_end = colum + (i - 1).ToString();
_workSheet.get_Range(coordinate_start, coordinate_end).Merge(_workSheet.get_Range(coordinate_start, coordinate_end).MergeCells);
_workSheet.get_Range(coordinate_start, coordinate_end).ColumnWidth = 2;
_workSheet.get_Range(coordinate_start, coordinate_end).WrapText = true;
rowSpanNum = 1;
break;
}
if (i == (num + 2))
{
string coordinate_start = colum + (i - rowSpanNum + 1).ToString();
string coordinate_end = colum + (i).ToString();
_workSheet.get_Range(coordinate_start, coordinate_end).Merge(_workSheet.get_Range(coordinate_start, coordinate_end).MergeCells);
_workSheet.get_Range(coordinate_start, coordinate_end).ColumnWidth = 2;
_workSheet.get_Range(coordinate_start, coordinate_end).WrapText = true;
}
}
}
}
// 产生下载效果导出Excel
private void SendFileToClient(HttpResponse Response)
{
try
{
//删除服务器端的临时文件
DeleteExcelFile();
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=ExportDataTable.xls");
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.ContentType = "application/ms-excel";
Response.BinaryWrite(Buffer);
Response.Flush();
Response.Close();
Response.End();
}
catch (Exception ex)
{
CloseExcle();
//throw (ex);
}
}
//清除内存中的Excle进程
private void CloseExcle()
{
if (this._workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(this._workBook);
this._workBook = null;
}
if (this.xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlApp);
this.xlApp = null;
}
GC.Collect();
}
//删除生成的Excel临时文件
private void DeleteExcelFile()
{
this._workBook.Save();
this._workBook.Close(missing, missing, missing);
this.xlApp.Quit();
CloseExcle();
FileStream MyFileStream = new FileStream(this.Report, FileMode.Open);
long FileSize = MyFileStream.Length;
Buffer = new byte[(int)FileSize];
MyFileStream.Read(Buffer, 0, (int)FileSize);
MyFileStream.Close();
FileInfo mode = new FileInfo(this.Report);
try
{
mode.Delete();
}
catch (Exception ex)
{
//throw (ex);
}
}
}
}
587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
