public void OutputExcel(DataView dv, string str)
{
//
// TODO: 在此处添加构造函数逻辑
//
//dv为要输出到Excel的数据,str为标题名称
GC.Collect();
Application excel= new Application();
int rowIndex = 2;
int colIndex = 0;
_Workbook xBk;
_Worksheet xSt;
//excel = new ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
//
//取得标题
//
foreach (DataColumn col in dv.Table.Columns)
{
colIndex++;
excel.Cells[2, colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[2, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}
//
//取得表格中的数据
//
foreach (DataRowView row in dv)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dv.Table.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
if (colIndex == 2)// 如何让他在当前的单元格里面插入图片?
{
xSt.Shapes.AddPicture(Server.MapPath("smallPhoto/" + row[col.ColumnName].ToString() + ".jpg"), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 10, 20, 30, 40);
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
int rowSum = rowIndex + 1; //加载一个合计行
int colSum = 1;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
//设置选中的部分的颜色
//
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Interior.ColorIndex = 19;//设置为浅黄色,共计有56种
//取得整个报表的标题
//
excel.Cells[1, 1] = str;
//
//设置整个报表的标题格式
//
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Bold = true;
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Select();
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, 1]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThin;//设置左边线
xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThin;//设置上边线
xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThin;//设置右边线
xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;//设置下边线
//
//显示效果
//
excel.Visible = true;
xBk.SaveCopyAs(Server.MapPath("./excel/货单.xlsx"));
dv = null;
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
string path = Server.MapPath("./excel/货单.xlsx");
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
}
解决方案 »
- oracle连接超出打开游标的问题
- 求助excel文档导入到sql server
- C#从后台给Repeater的itemPlate模板添加子控件
- javascript 切割了图片,点击了上传之后,我服务器端怎么生成用户刚才选择的图片
- 数据更新问题~
- 异常详细信息: System.IndexOutOfRangeException: title
- 关于Aspx页面图片显示问题 ,紧急求助
- dropdownlist怎样实现自动刷新
- 怎样给dataset中table的某子段赋值?
- 请问DATASET和DataReader之间除了DATASET可以同时使用多个TABLE之外,还有什么区别?
- 开发winform时,由于用户控件的类中代码长,想写在几个文件中,怎么写?
- 怎么扩展SqlMembershipProvider类中的CreateUser方法?
{
m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
m_objRange.Select();
float PicLeft, PicTop;
PicLeft = Convert.ToSingle(m_objRange.Left);
PicTop = Convert.ToSingle(m_objRange.Top);
m_objSheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);
} mySheet.Shapes.AddPicture(PicturePath,Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);