Excel文件的导入导出.docx
- 文档编号:11287707
- 上传时间:2023-02-26
- 格式:DOCX
- 页数:16
- 大小:18.15KB
Excel文件的导入导出.docx
《Excel文件的导入导出.docx》由会员分享,可在线阅读,更多相关《Excel文件的导入导出.docx(16页珍藏版)》请在冰豆网上搜索。
Excel文件的导入导出
#regionCorePlex32646
usingSystem;
usingSystem.Data;
usingSystem.Web;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Data.OleDb;
usingMicrosoft.Office.Interop.Excel;
usingSystem.Reflection;//ForMissing.ValueandBindingFlags
usingSystem.Runtime.InteropServices;//ForCOMException
///
///Excel的摘要说明
///
namespaceharry
{
publicclassExcel
{
#region读取Excel文件转换成DataTable
///
///读取Excel文件转换成DataTable
///
///
///
///
publicstaticSystem.Data.DataTableImport_Sheet(stringexcelPath,stringsheetName){returnImport_Sql(excelPath,"select*from["+sheetName+"$]");}
///
///读取Excel文件转换成DataTable
///
///
///
///
publicstaticSystem.Data.DataTableImport_Sql(stringexcelPath,stringsql)
{
//string2003="Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties=Excel8.0;datasource="+filepath;
//string2007="Provider=Microsoft.ACE.OLEDB.12.0;ExtendedProperties='Excel12.0;HDR=YES';datasource="+fPath;//读EXCEL2003/excel2007
OleDbConnectionconn=newOleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties='Excel8.0;HDR=YES;HDR=1;';datasource="+excelPath);
OleDbDataAdapterodda=newOleDbDataAdapter(sql,conn);
System.Data.DataTabledt=newSystem.Data.DataTable();
try
{
conn.Open();
odda.Fill(dt);
}
catch(Exceptionex){throwex;}
finally
{
conn.Close();
conn.Dispose();
odda.Dispose();
}
returndt;
}
#endregion
#region输出Excel文件
#region使用office组件输出
publicstaticvoidExport_Office(System.Data.DataTabledt){Export_Office(dt,"",newModel.harry.ExcelInfo());}
publicstaticvoidExport_Office(System.Data.DataTabledt,Model.harry.ExcelInfomodel){Export_Office(dt,"",model);}
publicstaticvoidExport_Office(System.Data.DataTabledt,stringsavePath){Export_Office(dt,savePath,newModel.harry.ExcelInfo());}
///
///输出Excel文件
///
///
///
///
privatestaticvoidExport_Office(System.Data.DataTabledt,stringsavePath,Model.harry.ExcelInfomodel)
{
if(dt.Rows.Count>0)
{
Microsoft.Office.Interop.Excel.Applicationexcel=newMicrosoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Worksheetformat=(Microsoft.Office.Interop.Excel._Worksheet)excel.Application.Workbooks.Add(true).ActiveSheet;
//设置行和列的索引
introwIndex=1,colIndex=0;
//添加列名
foreach(DataColumncolindt.Columns)
{
colIndex++;
excel.Cells[1,colIndex]=col.ColumnName;
}
//添加数据
foreach(DataRowrowindt.Rows)
{
rowIndex++;
colIndex=0;
foreach(DataColumncolindt.Columns)
{
colIndex++;
excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
}
}
if(model!
=null)
{
//设置标题是否为粗体
format.get_Range(excel.Cells[1,1],excel.Cells[1,colIndex]).Font.Bold=model.TitleBold;
Rangerange=format.get_Range(excel.Cells[1,1],excel.Cells[rowIndex,colIndex]);
//设置字体大小
range.Font.Size=model.FontSize;
//设置列宽
if(model.Width==Model.harry.ExcelInfo.WidthType.auto){range.Columns.AutoFit();}
elseif(model.Width==Model.harry.ExcelInfo.WidthType.size){range.ColumnWidth=model.WidthSize;}
//设置对齐格式
if(model.Align==Model.harry.ExcelInfo.AlignType.left){range.HorizontalAlignment=Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;}
elseif(model.Align==Model.harry.ExcelInfo.AlignType.center){range.HorizontalAlignment=Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;}
elseif(model.Align==Model.harry.ExcelInfo.AlignType.right){range.HorizontalAlignment=Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;}
else{}
if(model.Valign==Model.harry.ExcelInfo.ValignType.top){range.VerticalAlignment=Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;}
elseif(model.Valign==Model.harry.ExcelInfo.ValignType.middle){range.VerticalAlignment=Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;}
elseif(model.Valign==Model.harry.ExcelInfo.ValignType.bottom){range.VerticalAlignment=Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignBottom;}
else{}
}
//不可见,即后台处理
excel.Visible=false;
//设置禁止弹出保存的询问提示框
excel.DisplayAlerts=false;
////设置禁止弹出覆盖的询问提示框
//excel.AlertBeforeOverwriting=false;
try
{
if(savePath!
=""){excel.ActiveWorkbook.SaveCopyAs(savePath);}
else
{
excel.Save(AppDomain.CurrentDomain.BaseDirectory+"bak.xls");
if(System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory+"bak.xls"))System.IO.File.Delete(AppDomain.CurrentDomain.BaseDirectory+"bak.xls");
}
}
catch{}
finally
{
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
//释放使用的Excel对象
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
KillExcel();
}
}
}
//删除执行过程中未释放的Excel线程
privatestaticvoidKillExcel()
{
System.Diagnostics.Process[]excelProcesses=System.Diagnostics.Process.GetProcessesByName("EXCEL");
DateTimestartTime=newDateTime();
intprocessId=0;
for(inti=0;i { if(startTime { startTime=excelProcesses[i].StartTime; processId=i; } } if(excelProcesses[processId].HasExited==false){excelProcesses[processId].Kill();} } #endregion #region使用DataGrid输出 /// ///使用DataGrid输出Excel文件 /// /// publicstaticvoidExport_DataGrid(DataGriddg){Export_DataGrid(dg,null,null,null,false);} /// ///使用DataGrid输出Excel文件 /// /// /// publicstaticvoidExport_DataGrid(DataGriddg,stringfileName){Export_DataGrid(dg,null,fileName,null,false);} /// ///使用DataGrid输出Excel文件 /// /// /// /// publicstaticvoidExport_DataGrid(DataGriddg,stringfileName,stringformatCellIndex){Export_DataGrid(dg,null,fileName,formatCellIndex,false);} /// ///使用DataGrid输出Excel文件 /// /// /// /// /// /// publicstaticstringExport_DataGrid(DataGriddg,stringfileName,stringformatCellIndex,boolisWrite){returnExport_DataGrid(dg,null,fileName,formatCellIndex,isWrite);} /// ///使用DataGrid输出Excel文件 /// /// publicstaticvoidExport_DataGrid(System.Data.DataTabledt){Export_DataGrid(null,dt,null,null,false);} /// ///使用DataGrid输出Excel文件 /// /// /// publicstaticvoidExport_DataGrid(System.Data.DataTabledt,stringfileName){Export_DataGrid(null,dt,fileName,null,false);} /// ///使用DataGrid输出Excel文件 /// /// /// /// publicstaticvoidExport_DataGrid(System.Data.DataTabledt,stringfileName,stringformatCellIndex){Export_DataGrid(null,dt,fileName,formatCellIndex,false);} /// ///使用DataGrid输出Excel文件 /// /// /// /// /// /// publicstaticstringExport_DataGrid(System.Data.DataTabledt,stringfileName,stringformatCellIndex,boolisWrite){returnExport_DataGrid(null,dt,fileName,formatCellIndex,isWrite);} privatestaticstringExport_DataGrid(DataGriddg,System.Data.DataTabledt,stringfileName,stringformatCellIndex,boolisWrite) { if(dg==null) { dg=newDataGrid(); dg.DataSource=dt; dg.DataBind(); } foreach(DataGridItemdgiindg.Items) { for(intj=0;j { if(formatCellIndex! =null&&formatCellIndex! =""&&formatCellIndex.Contains("$"+j+"$"))dgi.Cells[j].Attributes.Add("style","mso-number-format: '@';"); } } if(fileName==null||fileName==""){fileName="Excel.xls";} System.IO.StringWriteroStringWriter=newSystem.IO.StringWriter(); dg.RenderControl(newSystem.Web.UI.HtmlTextWriter(oStringWriter)); if(isWrite){returnoStringWriter.ToString();} else { //this.EnableViewState=false; HttpResponseresponse=System.Web.HttpContext.Current.Response; response.Clear(); response.Buffer=true; response.ContentType="application/vnd.ms-excel"; response.Charset="gb2312"; response.ContentEncoding=System.Text.Encoding.GetEncoding("gb2312"); response.AppendHeader("content-disposition","attachment;fileName=\""+fileName+"\""); response.Write(oStringWriter.ToString()); response.End(); return""; } } #endregion #region使用GridView输出 /// ///使用GridView输出Excel文件 /// /// publicstaticvoidExport_GridView(GridViewgv){Export_GridView(gv,null,null,null,false);} ///
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 文件 导入 导出