using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
namespace CRM.Components
{
/**//// <summary>
/// Excel 的摘要说明
/// </summary>
public class ToExcel
{
public ToExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public static int TabletoExcel(DataTable TB, string[] cols)
{
try
{
if (TB.Rows.Count == 0)
{
//Response.Write("<script>alert('没有记录!!'</script>");
return 0;
}
Excel.Application xApp;//程序对象
Excel.Workbook xBook; //工作簿
Excel.Worksheet xSheet;//表单
xApp = new Excel.ApplicationClass();//新建对象
xApp.Visible = true;
if (xApp == null)
{
// Response.Write("<script>alert('创建对象出错，可能你没有安装office或你的office版本低'</script>");
return 0;
}
else
{
// Response.Write("<script>alert('创建对象成功')</script>");
// return;
}
xBook = xApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
xSheet = new Excel.Worksheet();
xSheet = (Excel.Worksheet)xBook.Sheets[1];
for (int i = 1; i <= cols.Length; i++)
{
Excel.Range rng = (Excel.Range)xSheet.Cells[1, i];
rng.Value2 = cols[i - 1].ToString();
}
//开始对电子表格操作
for (int i = 0; i <= TB.Rows.Count - 1; i++)
{
for (int j = 1; j <= cols.Length; j++)
{
Excel.Range rngbh = (Excel.Range)xSheet.Cells[i + 2, j];
rngbh.Value2 = TB.Rows[i][j - 1].ToString();
}
}
xBook = null;
xSheet = null;
// xApp.Quit();
xApp = null;
//Response.Write("<script>alert('导出到Excel文件成功')</script>");
return 1;
}
catch (Exception e2)
{
throw e2;
}
}
public static int TabletoExcel(DataTable TB)
{
if (TB.Rows.Count == 0)
{
//Response.Write("<script>alert('没有记录!!'</script>");
return 0;
}
Excel.Application xApp;//程序对象
Excel.Workbook xBook; //工作簿
Excel.Worksheet xSheet;//表单
xApp = new Excel.ApplicationClass();//新建对象
xApp.Visible = true;
if (xApp == null)
{
// Response.Write("<script>alert('创建对象出错，可能你没有安装office或你的office版本低'</script>");
return 0;
}
else
{
// Response.Write("<script>alert('创建对象成功')</script>");
// return;
}
xBook = xApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
xSheet = new Excel.Worksheet();
xSheet = (Excel.Worksheet)xBook.Sheets[1];
xSheet.get_Range(xSheet.Cells[1, 1], xSheet.Cells[1, 1]).ColumnWidth = 20;
xSheet.get_Range(xSheet.Cells[1, 2], xSheet.Cells[1, 2]).ColumnWidth = 20;
xSheet.get_Range(xSheet.Cells[1, 3], xSheet.Cells[1, 3]).ColumnWidth = 20;
//xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).ColumnWidth = 0;
for (int i = 1; i <= TB.Columns.Count; i++)
{
Excel.Range rng = (Excel.Range)xSheet.Cells[1, i];
rng.Value2 = TB.Columns[i - 1].ColumnName;
}
//开始对电子表格操作
for (int i = 0; i <= TB.Rows.Count - 1; i++)
{
for (int j = 1; j <= TB.Columns.Count; j++)
{
Excel.Range rngbh = (Excel.Range)xSheet.Cells[i + 2, j];
rngbh.Value2 = TB.Rows[i][j - 1].ToString();
}
}
xBook = null;
xSheet = null;
// xApp.Quit();
xApp = null;
//Response.Write("<script>alert('导出到Excel文件成功')</script>");
return 1;
}
/**//// <summary>
/// 导出Excel
/// </summary>
public static void tableToExcel(DataTable tb)
{
FileStream file;
StreamWriter filewrite;
Random r = new Random();
string t = r.NextDouble().ToString().Remove(0, 2);
string path = System.Web.HttpContext.Current.Server.MapPath(".") + @"\tempdate\excel.xls";
//System.Web.HttpContext.Current.Server.MapPath("tempdate") + "\\查询.xls";
int i, j;
// Response.Write(FilePath);
file = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);
filewrite = new StreamWriter(file, System.Text.Encoding.Unicode);
string strline = "";
//表列名
for (i = 1; i <= tb.Columns.Count; i++)
{
strline = strline + tb.Columns[i - 1].ColumnName.ToString() + Convert.ToChar(9);
}
filewrite.WriteLine(strline);
//表内容
for (i = 1; i <= tb.Rows.Count; i++)
{
strline = "";
for (j = 1; j <= tb.Columns.Count; j++)
{
strline = strline + tb.Rows[i - 1][j - 1].ToString() + Convert.ToChar(9);
}
filewrite.WriteLine(strline);
}
filewrite.Close();
file.Close();
string l_strHtml = "<script language='JavaScript'>";
l_strHtml += " window.open('./tempdate/excel.xls','newwindow','height=800,width=1024,scrollbars=yes,resizable=yes,location=yes, status=yes,menubar=yes,toolbar=yes,titlebar=yes')";
l_strHtml += "</script>";
HttpContext.Current.Response.Write(l_strHtml);
// window.showModalDialog('Activityinfo.aspx?activityID=1','newwindow','height=400,width=400,help=0;status=0;menubar=0;')
// HttpContext.Current.Response.Redirect("查询.xls");
}
public static void tableToExcel(DataTable tb, string listname)
{
FileStream file;
StreamWriter filewrite;
Random r = new Random();
string t = r.NextDouble().ToString().Remove(0, 2);
string path = System.Web.HttpContext.Current.Server.MapPath(".") + @"\tempdate\excel.xls";
int i, j;
// Response.Write(FilePath);
file = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);
filewrite = new StreamWriter(file, System.Text.Encoding.Unicode);
string strline = "";
filewrite.WriteLine(listname);
//表内容
for (i = 1; i <= tb.Rows.Count; i++)
{
strline = "";
for (j = 1; j <= tb.Columns.Count; j++)
{
strline = strline + tb.Rows[i - 1][j - 1].ToString() + Convert.ToChar(9);
}
filewrite.WriteLine(strline);
}
filewrite.Close();
file.Close();
string l_strHtml = "<script language='JavaScript'>";
l_strHtml += " window.open('./tempdate/excel.xls','newwindow','height=800,width=1024,scrollbars=yes,resizable=yes,location=yes, status=yes,menubar=yes,toolbar=yes,titlebar=yes')";
l_strHtml += "</script>";
HttpContext.Current.Response.Write(l_strHtml);
// window.showModalDialog('Activityinfo.aspx?activityID=1','newwindow','height=400,width=400,help=0;status=0;menubar=0;')
// HttpContext.Current.Response.Redirect("查询.xls");
}
//listname 和 cols 个数 要相等 ，顺序一一对应
public static void tableToExcel(DataTable tb, string[] listname, string[] cols)
{
if ((tb == null) || (tb.Rows.Count == 0))
{
return;
}
FileStream file;
StreamWriter filewrite;
Random r = new Random();
string t = r.NextDouble().ToString().Remove(0, 2);
string path = System.Web.HttpContext.Current.Server.MapPath(".") + @"\tempdate\excel.xls";
int i, j;
// Response.Write(FilePath);
file = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);
filewrite = new StreamWriter(file, System.Text.Encoding.Unicode);
string strline = "";
for (i = 1; i <= listname.Length; i++)
{
strline = strline + listname[i - 1].ToString() + Convert.ToChar(9);
}
filewrite.WriteLine(strline);
//表内容
for (i = 1; i <= tb.Rows.Count; i++)
{
strline = "";
//strline = strline + tb.Rows[i - 1][cols[0].ToString()].ToString() + Convert.ToChar(9);
//strline = strline + tb.Rows[i - 1][cols[1].ToString()].ToString() + Convert.ToChar(9);
for (j = 1; j <= cols.Length; j++)
{
strline = strline + tb.Rows[i - 1][cols[j - 1].ToString()].ToString() + Convert.ToChar(9);
}
filewrite.WriteLine(strline);
}
filewrite.Close();
file.Close();
string l_strHtml = "<script language='JavaScript'>";
l_strHtml += " window.open('./tempdate/excel.xls','newwindow','height=800,width=1024,scrollbars=yes,resizable=yes,location=yes, status=yes,menubar=yes,toolbar=yes,titlebar=yes')";
l_strHtml += "</script>";
HttpContext.Current.Response.Write(l_strHtml);
// window.showModalDialog('Activityinfo.aspx?activityID=1','newwindow','height=400,width=400,help=0;status=0;menubar=0;')
// HttpContext.Current.Response.Redirect("查询.xls");
}
public static void SendDataToExcel(DataTable dt)
{
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
int colindex = 0;
foreach (DataColumn col in dt.Columns)
{
colindex++;
excel.Cells[1, colindex] = col.ColumnName;
}
int rowindex = 1;
foreach (DataRow row in dt.Rows)
{
rowindex++;
colindex = 0;
foreach (DataColumn col in dt.Columns)
{
colindex++;
excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
}
}
excel.Visible = true;
}
}
}
posted on
2006-05-23 09:52
张志
阅读(
1054)
评论()
编辑
收藏
举报
