作者：乔山办公网日期：
返回目录：excel表格制作
代码发到那？
/// <summary>
/// 导出DataGridView的数据到Excel表中
/// </summary>
/// <param name="m_DataView"></param>
public static void DataToExcel(DataGridView m_DataView)
{
SaveFileDialog kk = new SaveFileDialog();
kk.Title = "保存EXECL文件";
kk.Filter = "EXECL文件(*.xls) |*.xls|所有文件(*.*) |*.*";
kk.FilterIndex = 1;
if (kk.ShowDialog() == DialogResult.OK)
{
string FileName = kk.FileName;// + ".slsx";
if (File.Exists(FileName))
File.Delete(FileName);
FileStream objFileStream;
StreamWriter objStreamWriter;
string strLine = "";
objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);
for (int i = 0; i < m_DataView.Columns.Count; i++)
{
if (m_DataView.Columns[i].Visible == true)
{
strLine = strLine + m_DataView.Columns[i].HeaderText.ToString() + Convert.ToChar(9);
}
}
objStreamWriter.WriteLine(strLine);
strLine = "";
for (int i = 0; i < m_DataView.Rows.Count; i++)
{
if (m_DataView.Columns[0].Visible == true)
{
if (m_DataView.Rows[i].Cells[0].Value == null)
strLine = strLine + " " + Convert.ToChar(9);
else
strLine = strLine + m_DataView.Rows[i].Cells[0].Value.ToString() + Convert.ToChar(9);
}
for (int j = 1; j < m_DataView.Columns.Count; j++)
{
if (m_DataView.Columns[j].Visible == true)
{
if (m_DataView.Rows[i].Cells[j].Value == null)
strLine = strLine + " " + Convert.ToChar(9);
else
{
string rowstr = "";
rowstr = m_DataView.Rows[i].Cells[j].Value.ToString();
if (rowstr.IndexOf("\r\n") > 0)
rowstr = rowstr.Replace("\r\n", " ");
if (rowstr.IndexOf("\t") > 0)
rowstr = rowstr.Replace("\t", " ");
strLine = strLine + rowstr + Convert.ToChar(9);
}
}
}
objStreamWriter.WriteLine(strLine);
strLine = "";
}
objStreamWriter.Close();
objFileStream.Close();
}
}
调用e799bee5baa6e997aee7ad94e4b893e5b19e363
Main_BodyExcel.DataToExcel(gwSum);
<HTML>
<HEAD>
<title>WEB页面导出为EXCEL文档的方法
</title>
</HEAD>
<body>
<BR>
<table id = "PrintA" width="100%" border="1" cellspacing="0" cellpadding="0" bgcolor = "#61FF13">
<TR style="text-align : center;">
<TD>单元格A</TD>
<TD>单元格A</TD>
<TD>单元格A</TD>
<TD>单元格A</TD>
</TR>
<TR>
<TD colSpan=4 style="text-align : center;"><font color="BLUE" face="Verdana">单元格合并行A</FONT></TD>
</TR>
</TABLE>
<BR>
<table id = "PrintB" width="100%" border="1" cellspacing="0" cellpadding="0">
<TR style="text-align : center;">
<TD>单元格B</TD>
<TD>单元格B</TD>
<TD>单元格B</TD>
<TD>单元格B</TD>
</TR>
<TR>
<TD colSpan=4 style="text-align : center;">单元格合并行B</TD>
</TR>
</TABLE>
<input type="button" onclick="javascript:AllAreaWord();" value="导出页面指定区域内容到e799bee5baa6e79fa5e98193e59b9ee7ad94366Word">
<input type="button" onclick="javascript:AllAreaExcel();" value="导出页面指定区域内容到Excel">
<input type="button" onclick="javascript:CellAreaExcel();" value="导出表单单元格内容到Excel">
<SCRIPT LANGUAGE="javascript">
//指定页面区域内容导入Excel
function AllAreaExcel()
{
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var sel=document.body.createTextRange();
sel.moveToElementText(PrintA);
sel.select();
sel.execCommand("Copy");
oSheet.Paste();
oXL.Visible = true;
}
//指定页面区域“单元格”内容导入Excel
function CellAreaExcel()
{
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var Lenr = PrintA.rows.length;
for (i=0;i<Lenr;i++)
{
var Lenc = PrintA.rows(i).cells.length;
for (j=0;j<Lenc;j++)
{
oSheet.Cells(i+1,j+1).value = PrintA.rows(i).cells(j).innerText;
}
}
oXL.Visible = true;
}
//指定页面区域内容导入Word
function AllAreaWord()
{
var oWD = new ActiveXObject("Word.Application");
var oDC = oWD.Documents.Add("",0,1);
var oRange =oDC.Range(0,1);
var sel = document.body.createTextRange();
sel.moveToElementText(PrintA);
sel.select();
sel.execCommand("Copy");
oRange.Paste();
oWD.Application.Visible = true;
//window.close();
}
</SCRIPT>
</body></html>
先引用
先引用
using System.Security;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Configuration;
直接导出方法636f7079e799bee5baa6e79fa5e981933351.
public override void VerifyRenderingInServerForm(Control control)//导出Excel必须加整个不然会报错
{
}
protected void Button2_Click(object sender, EventArgs e)
{
GV1.Visible = true;
string filename = "SendMailHistory";
Response.Clear();
Response.Buffer = true;
Response.Charset = "UTF-8";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.ContentType = "application/vnd.ms-excel; charset=UTF-8";
EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
GV1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
方法2用模版
protected void Button4_Click(object sender, EventArgs e)
{
string save_path = "", tick = "";
ExcelOperate excelOperate = new ExcelOperate();
string temp_path = Server.MapPath("~/DownLoad/Report");//生成的文件存放路径
string loadfilename=Server.MapPath("~/DownLoad/Report/SendMailHistory.xlsx");
string template_path = Server.MapPath("~/designer");//模板路径
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
Directory.CreateDirectory(template_path);
}
try
{
if (File.Exists(loadfilename))
{
File.Delete(loadfilename);
}
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
}
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(template_path + "\\sendmailhistory.xlsx");
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
}
//======数据写入excel
int rowNum = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
rowNum = i + 1;
worksheet.Cells[6 + i, 1] = rowNum;
worksheet.Cells[6 + i, 2] = dt.Rows[i]["wtemp"].ToString();
worksheet.Cells[6 + i, 3] = dt.Rows[i]["YP_FormBG_ID"].ToString();
worksheet.Cells[6 + i, 4] = dt.Rows[i]["PartNO"].ToString();
worksheet.Cells[6 + i, 5] = dt.Rows[i]["ModelName"].ToString();
worksheet.Cells[6 + i, 6] = dt.Rows[i]["SendUser"].ToString();
// worksheet.get_Range(worksheet.Cells[14 + i, 7], worksheet.Cells[14 + i, 8]).Merge(); //合并单元格
worksheet.Cells[6 + i, 7] = dt.Rows[i]["SendDate"].ToString();
worksheet.Cells[6 + i, 8] = dt.Rows[i]["Mark1"].ToString();
worksheet.get_Range(worksheet.Cells[6 + i, 1], worksheet.Cells[6 + i, 8]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
worksheet.get_Range(worksheet.Cells[6 + i, 1], worksheet.Cells[6 + i, 8]).Borders.LineStyle = XlLineStyle.xlContinuous;
}
worksheet.Cells[rowNum + 6 + 1, 3] = "编制：";//worksheet.Cells[rowNum +6 +1, 3]
worksheet.Cells[rowNum + 6 + 1, 5] = "审批：";
worksheet.Cells[rowNum + 6 + 1, 7] = "WL4E300002_AE";
worksheet.get_Range(worksheet.Cells[rowNum + 6 + 1, 7], worksheet.Cells[rowNum + 6 + 1, 8]).Merge(); //合并单元格
tick = DateTime.Now.Ticks.ToString();
save_path = loadfilename;
workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
labmsg.Text = "导出Excel成功";
Response.Redirect("~/DownLoad/Report/SendMailHistory.xlsx");
}
catch (Exception ex)
{
labmsg.Text = "";
labmsg.ForeColor = Color.Red;
Response.Redirect("~/DownLoad/Report/SendMailHistory.xlsx");
// s = false;
}
}