小弟新手,在练习一个小项目,遇到了个问题,想请教大家项目中几个页面,应该是需要用到报表,进行一个类似填写个人信息的页面,并且在填写完成后,需要导出为xls文件,中间不需要把数据保存到数据库,仅仅是一个填写to导出的过程,但是还有个问题,有好多种报表格式,且都是有些复杂的报表,就是想问下大家,有没有什么较为便捷的方法,来解决这个问题,我在网上找了些教程,要么是不合适,要么是我能力有限,呵呵,实在是找不到解决方法了,希望大家不惜指教!谢谢!
过程简介:XX.aspx--报表填写(不规则格式)--导出Excel(xls,且无需保存到数据库)
过程简介:XX.aspx--报表填写(不规则格式)--导出Excel(xls,且无需保存到数据库)
/// 导出到excel模板add by gfl 20110414
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExcel_Click(object sender, EventArgs e)
{
//检查是否安装excel程序
if (!codeboolisExcelInstalled())
{
MessageBox.Show("当前系统没有发现可执行的Excel文件, 如需使用Excel功能请先安装office", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (this.dgvITList.Rows.Count - 1 == 0)
{
MessageBox.Show("销售数据未填写，无法导出EXCEL文件!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
for (int colNumb = 0; colNumb < dgvITList.Rows.Count - 1; colNumb++)
{
//判断是否有 数量、价格、金额合计没有填写完整的行
if (dgvITList.Rows[colNumb].Cells["Number"].Value == null || dgvITList.Rows[colNumb].Cells["colRealPrice"].Value == null || dgvITList.Rows[colNumb].Cells["Column17"].Value == null)
{
MessageBox.Show("销售数据未填写完整，请将数据填写完整!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
//以顾客名称加上日期来命名要保存到的文件夹
string Path1 = Application.StartupPath + "\\SaveExcel";
string Path2 = Application.StartupPath + "\\ExcelModal";
if (!Directory.Exists(Path1))//如果不存在就创建SaveExcel 文件夹
{
Directory.CreateDirectory(Path1);
}
if (!Directory.Exists(Path2))//如果不存在就创建ExcelModal文件夹
{
Directory.CreateDirectory(Path2);
} //判断模板文件是否存在？
string path2;//源文件路径名称
saveFileDialog1.FileName = cbxCustomer.Text + DateTime.Now.ToString("yyyymmdd") + "销售清单";
saveFileDialog1.Title = "销售报表存放位置";
saveFileDialog1.Filter = "excel files(*.xls)|*.xls";
saveFileDialog1.FilterIndex = 0;
//以顾客名称加上日期来命名要保存到的文件夹
saveFileDialog1.InitialDirectory = Application.StartupPath + "\\SaveExcel";
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.Cancel)
{
return;
}
path2 = saveFileDialog1.FileName;
ExcelFileCopy(path2);//复制模板到用户指定路径 if (path2 == null)
{
MessageBox.Show("Excel模板文件路径不能为空！", "系统信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
return;
}
if (!File.Exists(path2))
{
MessageBox.Show("指定路径的Excel模板文件不存在！", "系统信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
return;
}
templetFile = path2;
//C#创建Excel文件之取得数据
DataTable dtE = GetData();
if (dtE != null)
{
int rowCount = dtE.Rows.Count;
DataTableToExcel(dtE);
}
this.Cursor = Cursors.Default;
}
/// <summary>
/// 10进制到Excel的26进制的转换函数
/// </summary>
/// <param name="i"></param>
/// <returns></returns>
private string convertToCharacter(int i)
{
char[] list = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
StringBuilder sb = new StringBuilder();
while ((i - 1) / 26 != 0)
{
sb.Append(list[i / 26 - 1]);
i = i % 26;
}
i = (i - 1) % 26;
sb.Append(list[i]);
ColNum = sb.ToString();
return ColNum;
}
/// <summary>
/// Excel模板复制add by gfl20110414
/// </summary>
/// <param name="path">源文件路径以及文件名称</param>
/// <param name="path2">目标路径以及文件名称</param>
/// <returns></returns>
public void ExcelFileCopy(string path2)
{
string path = Application.StartupPath + @"\ExcelModal\销售清单.xls";
FileInfo fi1 = new FileInfo(path);
FileInfo fi2 = new FileInfo(path2);
try
{
if (fi2.Exists)
{
fi2.Delete();
}
fi1.CopyTo(path2);//复制模板到指定路径
}
catch
{
Console.WriteLine("模板复制失败！");
}
}
//判断本机是否安装Excel文件方法
private bool codeboolisExcelInstalled()
{
Type type = Type.GetTypeFromProgID("Excel.Application");
return type != null;
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns></returns>
private DataTable GetData()
{
System.Data.DataTable dtExcel = new DataTable();
//添加dtExcel列
dtExcel.Columns.Add("名称", typeof(string));
dtExcel.Columns.Add("件数", typeof(int));
dtExcel.Columns.Add("数量", typeof(int));
dtExcel.Columns.Add("单价", typeof(decimal));
dtExcel.Columns.Add("金额", typeof(decimal));
for (int colNum = 0; colNum < dgvITList.Rows.Count - 1; colNum++)
{
DataRow dr = dtExcel.NewRow();
//将datagridview中某行某列的值添加到字段中
dr["名称"] = dgvITList.Rows[colNum].Cells["Column3"].Value;
if (dgvITList.Columns["Group1"].Visible == true && dgvITList.Rows[colNum].Cells["Group1"].Value != null)
{ dr["件数"] = ComLibrary.ToInt(dgvITList.Rows[colNum].Cells["Group1"].Value);
}
else
{
dr["件数"] = 0;
}
dr["数量"] = dgvITList.Rows[colNum].Cells["Number"].Value.ToString().Replace(",", "");
dr["单价"] = dgvITList.Rows[colNum].Cells["colRealPrice"].Value.ToString().Replace(",", "");
dr["金额"] = dgvITList.Rows[colNum].Cells["Column17"].Value.ToString().Replace(",", "");
dtExcel.Rows.Add(dr);//将dgvITList的一行添加到dtExcel的行中
}
return dtExcel;
}
}
}
生成excel文件必须看懂。
最简单的方法是复制网页,再粘贴到excel里面,然后用js调用excel的宏,利用宏来调整格式.
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName1.xls");
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/excel";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
t.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString());
Response.Flush();
Response.End();
也可以借助报表来导出也可以构造DataTable 不过复杂的表头好像很麻烦老贴
var oXL=new ActiveXObject("Excel.Application");
var oWB=oXL.Workbooks.Add();
var oSheet=oWB.ActiveSheet;
var sel=document.body.createTextRange();
sel.moveToElementText(idDataObj);
sel.select;
sel.execCommand("Copy");
oSheet.paste();
oXL.Visible=true;
oSheet=null;
oWB=null;
appExcel=null;
}比如<input type="button" onclick=exportToExcel(divData) value="Excel" />
<div id="divExcel">
<TABLE cellSpacing=0 cellPadding=3 width="95%" border=0>
<TBODY>
<TR>
<TD style="PADDING-BOTTOM: 10px" align=middle colSpan=6><FONT face=黑体
size=4>标题</FONT></TD></TR></TBODY></TABLE>
<TABLE id=tblData style="BORDER-COLLAPSE: collapse" borderColor=#000000
cellSpacing=0 cellPadding=5 width="95%" border=1>
<TBODY>
<TR vAlign=center align=middle>
<TD noWrap>薪资等级</TD>
<TD noWrap>基本工资</TD>
<TD noWrap>考勤奖金</TD>
<TD noWrap>考勤奖金扣减</TD>
<TD noWrap>绩效奖金</TD>
<TD noWrap>绩效调增</TD>
<TD noWrap>绩效调减</TD>
<TD noWrap>计量津贴</TD>
<TD noWrap>工资调增</TD>
<TD noWrap>工资调减</TD>
<TD noWrap>代扣项目</TD>
</TR>
<TR vAlign=center align=middle onMouseOut="this.style.background='';" onMouseOver="this.style.background='#EEE';">
<TD noWrap>零级</TD>
<TD noWrap>-</TD>
<TD noWrap>-</TD>
<TD noWrap>-</TD>
<TD noWrap>-</TD>
<TD noWrap>-</TD>
<TD noWrap>-</TD>
<TD noWrap>-</TD>
<TD noWrap>-</TD>
<TD noWrap>-</TD>
<TD noWrap>-</TD>
</TR>
</TBODY></TABLE>
</div>
这里写错了