急急!!ASP.NET导出EXCEL,导出的EXCEL再导入时说外部表不是预期格式

zwx_1990 2010-08-05 11:22:05
public void OutputExcel(DataView dv, string str)
{

dv为要输出到Excel的数据,str为标题名称

GC.Collect();
Excel.Application excel;// = new Application();

int rowIndex = 4;


int colIndex = 1;

Excel._Workbook xBk;

Excel._Worksheet xSt;

excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;

取得标题

foreach (DataColumn col in dv.Table.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;

xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}



取得表格中的数据



foreach (DataRowView row in dv)
{

rowIndex++;

colIndex = 1;

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 = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐

}

else
{

if (col.DataType == System.Type.GetType("System.String"))
{

excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();

xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}

else
{

excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();

}
}
}

}



加载一个合计行



int rowSum = rowIndex + 1;

int colSum = 2;

excel.Cells[rowSum, 2] = "合计";

xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.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[2, 2] = str;



设置整个报表的标题格式



xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;

xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;



设置报表表格为最适应宽度



xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();

xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();



设置整个报表的标题为跨列居中



xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();

xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;



绘制边框



xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;

xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗

xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗

xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗

xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗



显示效果



excel.Visible=true;

xSt.Export(Server.MapPath(".")+""""+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);

xBk.SaveCopyAs(Server.MapPath(".") +this.xlfile.Text + ".xls");

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(".") + this.xlfile.Text + ".xls";// Server.MapPath(this.xlfile.Text + ".xls");

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();

} 以上是导出



public DataTable InputExcel(string Path, string TableName, string tablename2)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + "; Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";Persist Security Info = FALSE;";
OleDbConnection conn = new OleDbConnection(strConn);
try
{

string strExcel = "select * from [" + TableName + "$]";
OleDbCommand cm1 = new OleDbCommand(strExcel, conn);

OleDbDataAdapter myCommand = new OleDbDataAdapter(cm1);
if (tablename2.Length > 0 && !tablename2.Equals(string.Empty))
TableName = tablename2;
DataTable ds = new DataTable();
Session["fff"] = ds.DefaultView;
conn.Open();
myCommand.Fill(ds);

return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Close();
}
}以上是导入
...全文
426 点赞 收藏 4
写回复
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
风2013 2011-04-08
问题出在导出的方法上,我先前也出现过类似的问题,建议你在去重新找个导出的方法
我这里有个导出的方法,你可以看看
public static void Export(GridView gv, string filename)
{
XlsDocument doc = new XlsDocument();
doc.FileName = filename;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cells cells = sheet.Cells;
ColumnInfo cinfo = new ColumnInfo(doc, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 10;//列结束
cinfo.Collapsed = true;
cinfo.Width = 19 * 387;//列宽度
sheet.AddColumnInfo(cinfo);

//填充标题行
Cell cell;
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
cell = sheet.Cells.Add(1, 1 + i, gv.HeaderRow.Cells[i].Text);
}

//填充内容
Cell cll;
for (int i = 0; i < gv.Rows.Count; i++)
{
for (int j = 0; j < gv.Columns.Count; j++)
{
if (gv.Rows[i].Cells[j].Text.ToString().IndexOf("×") >= 0)
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString().Replace("×", "x"));
}
else
{
cll = sheet.Cells.Add(i + 2, j + 1, gv.Rows[i].Cells[j].Text.ToString());
}
cll.Font.FontFamily = FontFamilies.Roman;
//cll= sheet.Cells.Add(i + 2, j + 1, this.grdShop.Rows[i].Cells[j].ToString());
}
}

doc.Send();
}
回复
zwx_1990 2010-08-05
那可不可以判断导入的EXCEL是03版本的还是07版本的
回复
zwx_1990 2010-08-05
去哪找啊
回复
重新去找一个导出导入的代码
回复
发动态
发帖子
ASP
创建于2007-09-28

2.8w+

社区成员

ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
申请成为版主
社区公告
暂无公告