用Io、Response.Write写的方式，能提供用户选择要保存的路径，但是导出的Excel不能设置单元格宽度、背景颜色..等等
用Microsoft.Office.Interop.Excel 的方式能设置单元格的属性，但是不能提供用户选择保存路径！ 有谁能把这两种方式的优势 结合在一起，给出一个新的解决方案。
最好是有具体的案例，
谢谢！
用Microsoft.Office.Interop.Excel 的方式能设置单元格的属性，但是不能提供用户选择保存路径！ 有谁能把这两种方式的优势 结合在一起，给出一个新的解决方案。
最好是有具体的案例，
谢谢！
就是利用Microsoft.Office.Interop 读写Excel，代码如下 绘画Excel表格并添加内容
/// <summary>
/// 创建Excel
/// </summary>
/// <param name="fileName">文件名称</param>
/// <param name="sheetName">sheet名称</param>
private void CreateExcel(string fileName, string sheetName,string path)
{
//定义一个数组 存储 Excel表的列标志 A-Z
String[] excelColuns = new String[20] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N" ,"O","P","Q","R","S","T"}; Microsoft.Office.Interop.Excel.ApplicationClass my = new Microsoft.Office.Interop.Excel.ApplicationClass();
my.Visible = true;
object objMissing = System.Reflection.Missing.Value; //打开工作簿
Microsoft.Office.Interop.Excel.Workbook mybook =(Microsoft.Office.Interop.Excel.Workbook)my.Workbooks.Add(1); // 1表示只建一个表
//mybook.Worksheets.Add(objMissing,objMissing,1,objMissing);//添加sheet
if (sheetName == "")
{
sheetName = "steet1";
}
else
{
((Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1]).Name = sheetName;//将sheet1的名称改为zhu
}
Microsoft.Office.Interop.Excel.Worksheet mysheet =(Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
((Microsoft.Office.Interop.Excel.Range)mysheet.Cells[2, 3]).EntireRow.Insert(0, 0); //添加行--添加要显示的内容
Microsoft.Office.Interop.Excel.Range excelRage = mysheet.get_Range("A1", excelColuns[repDeatail.Count]+"1");
excelRage.MergeCells = true;
excelRage.Select();
excelRage.RowHeight = "26";
excelRage.Cells[1, 1] = excelTitle;
excelRage.Font.Name = "宋体";
excelRage.Font.Size = "14";
excelRage.HorizontalAlignment =Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excelRage.VerticalAlignment =XlHAlign.xlHAlignCenter;
excelRage.Font.Bold =true ;
//添加表头信息
int outCount = 1;
int c = 0;
int RowCount=2;
//int Rows=0;
//if ((printData.Tables[0].Columns.Count + 1) % 3 == 0)
// Rows = printData.Tables[0].Columns.Count / 3;
//else
// Rows = printData.Tables[0].Columns.Count / 3 + 1;
if (repH!=null)
for (int i = 0; i < repH.Tables[0].Columns.Count; i++)
{
//Range rage2 = mysheet.get_Range(excelColuns[c] + (i + 1).ToString().Trim(), excelColuns[c + 1] + (i + 2).ToString().Trim());
try
{
Range rage2 = mysheet.get_Range(excelColuns[c] + RowCount, excelColuns[c + 1] + RowCount);
rage2.MergeCells = true;
//mysheet.Cells[excelColuns[c]+(i+1).ToString().Trim(),excelColuns[c+1]+(i+2).ToString().Trim()];
//RowCount, c + 1
rage2.Cells[1, 1] = repHerad[i].Result + ":" + repH.Tables[0].Rows[0][repHerad[i].Colun].ToString(); //if (c > printData.Tables[0].Columns.Count || printData.Tables[0].Columns.Count - c <= 3)
if (outCount == (printData.Tables[0].Columns.Count+1)/3)
{
//if (outCount == 1)
//{
RowCount = RowCount + 2;
c = 0;
outCount = 1;
rage2.Font.Name = "宋体";
rage2.Font.Size = "9";
continue;
//}
}
c = c + 3;
outCount = outCount + 1;
rage2.Font.Name = "宋体";
rage2.Font.Size = "9";
}
catch
{ }
}
//绘制明细表格 及样式
int rowNow =0;
//if ((printData.Tables[0].Columns.Count+1)%3==0)
// rowNow = RowCount;
//else
rowNow = (printData.Tables[0].Columns.Count+1) / 3;
if (repHerad.Count % rowNow==0)
rowNow = repHerad.Count / rowNow;
else
rowNow = repHerad.Count / rowNow+1;
rowNow = rowNow * 2+2;
Range exRange = mysheet.get_Range("A" + rowNow.ToString(), excelColuns[repDeatail.Count] + (rowNow+ printData.Tables[0].Rows.Count).ToString());
exRange.Borders.LineStyle = "1";
exRange.Font.Name = "宋体";
exRange.Font.Size = "9";
//exRange.EntireColumn.AutoFit();
mysheet.get_Range("A" + rowNow.ToString(), "A" + (rowNow + printData.Tables[0].Rows.Count).ToString()).HorizontalAlignment = XlHAlign.xlHAlignCenter;
mysheet.get_Range("A" + rowNow.ToString(), "A" + (rowNow + printData.Tables[0].Rows.Count).ToString()).VerticalAlignment = XlHAlign.xlHAlignCenter;
mysheet.get_Range("A" + rowNow.ToString(), excelColuns[repDeatail.Count] + rowNow.ToString()).HorizontalAlignment = XlHAlign.xlHAlignCenter;
mysheet.get_Range("A"+rowNow.ToString(), excelColuns[repDeatail.Count] +rowNow.ToString()).VerticalAlignment = XlHAlign.xlHAlignCenter; mysheet.get_Range("A" + rowNow.ToString(), excelColuns[repDeatail.Count] +rowNow.ToString()).EntireColumn.AutoFit();
//添加明细信息
for (int i = 0; i <= repDeatail.Count; i++)//表格 表头(标题)
{
if (i == 0)
{
//if (repDeatail[0].Result != "编号")
//{
//exRange.ColumnWidth = 6;
exRange.Cells[1, i + 1] = "编号";
continue;
//}
}
exRange.Cells[1, i + 1] = repDeatail[i-1].Result;
//exRange.ColumnWidth = repDeatail[i - 1].Withd*10;
//exRange.WrapText = true;
}
//表格中填写内容
for (int i = 0; i < printData.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < printData.Tables[0].Columns.Count; j++)
{
if (j == 0)
{
exRange.Cells[i + 2, j+1] = i + 1;
continue;
}
exRange.Cells[i + 2, j + 1] = printData.Tables[0].Rows[i][repDeatail[j-1].Colun].ToString();
}
}
//保存 "d:\\" + fileName + ".xls"
mybook.SaveAs(path, objMissing, objMissing, objMissing,
//Excel.XlSaveAsAccessMode.xlShared
objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
objMissing, objMissing, objMissing,
objMissing, objMissing);
mybook = null;
my.Quit();
my = null;
} }
再在相应的 单元格添加输出内容就可以了，代码如上；
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Drawing;
using System.IO;/// <summary>
///ExcelData 的摘要说明
/// </summary>
public class ExcelData
{
public ExcelData()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
public static void Export(System.Web.UI.Page page, System.Data.DataTable tab, string FileName)
{
System.Web.HttpResponse httpResponse = page.Response;
DataGrid dataGrid = new DataGrid();
dataGrid.DataSource = tab.DefaultView;
dataGrid.AllowPaging = false;
dataGrid.HeaderStyle.ForeColor = Color.White;
dataGrid.HeaderStyle.BackColor = Color.FromName("#aaaadd");
dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
dataGrid.HeaderStyle.Font.Bold = true;
dataGrid.DataBind();
httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" +
HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); //filename="*.xls";
httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
httpResponse.ContentType = "application/ms-excel";
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
dataGrid.RenderControl(hw); string filePath = page.Server.MapPath(" .") + "\\Files\\" + FileName;
if (!Directory.Exists(Path.GetDirectoryName(filePath)))
Directory.CreateDirectory(Path.GetDirectoryName(filePath)); System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);
sw.Write(tw.ToString());
sw.Close(); DownFile(httpResponse, FileName, filePath); httpResponse.End();
}
private static bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath)
{
try
{
Response.ContentType = "application/octet-stream"; Response.AppendHeader("Content-Disposition", "attachment;filename=" +
HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312");
System.IO.FileStream fs = System.IO.File.OpenRead(fullPath);
long fLen = fs.Length;
int size = 102400;//每100K同时下载数据
byte[] readData = new byte[size];//指定缓冲区的大小
if (size > fLen) size = Convert.ToInt32(fLen);
long fPos = 0;
bool isEnd = false;
while (!isEnd)
{
if ((fPos + size) > fLen)
{
size = Convert.ToInt32(fLen - fPos);
readData = new byte[size];
isEnd = true;
}
fs.Read(readData, 0, size);//读入一个压缩块
Response.BinaryWrite(readData);
fPos += size;
}
fs.Close();
System.IO.File.Delete(fullPath);
return true;
}
catch
{
return false;
}
}
}
这足够你用了。。
三个参数：1，当前面，2，从数据库查询出来获取的一第表。3.文件名他会让客户自己选择保存的路径。
=>
这个就是<td colspan=?
{
Response.Clear();
Response.Buffer = true;
this.Page.Visible = false;
Response.Charset = "gb2312";
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
Response.ContentEncoding = System.Text.Encoding.Default;//设置输出流为简体中文
Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
CultureInfo myCItrad = new CultureInfo("ZH-CN", true);
StringWriter oStringWriter = new StringWriter(myCItrad);
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
control.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.Flush();
Response.Close();
}
使用
string path = Server.MapPath("~/") + "";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.GetEncoding("utf-8")));
Response.ContentType = "application/octet-stream";
Response.WriteFile("" + path + "");
Response.End();
输出文件
string filePath = "d:\\xls\\" + System.DateTime.Now.ToString().Replace(":", "") + "广告播放.xls";
xlWorkBook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);#region 导出到客户端
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("广告播放", System.Text.Encoding.UTF8) + ".xls");
Response.ContentType = "Application/excel";
Response.WriteFile(filePath);
Response.End();
#endregion
KillProcessexcel("EXCEL");