Excel文件的导入导出文档格式.docx
《Excel文件的导入导出文档格式.docx》由会员分享,可在线阅读,更多相关《Excel文件的导入导出文档格式.docx(16页珍藏版)》请在冰点文库上搜索。
///读取Excel文件转换成DataTable
paramname="
excelPath"
>
Excel文件路径<
/param>
sheetName"
<
returns>
/returns>
publicstaticSystem.Data.DataTableImport_Sheet(stringexcelPath,stringsheetName){returnImport_Sql(excelPath,"
select*from["
+sheetName+"
$]"
);
}
sql"
Excel内查询语句<
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'
;
+fPath;
//读EXCEL2003/excel2007
OleDbConnectionconn=newOleDbConnection("
Excel8.0;
HDR=YES;
HDR=1;
'
+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文件
dt"
数据源<
savePath"
储存路径<
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;
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;
//不可见,即后台处理
excel.Visible=false;
//设置禁止弹出保存的询问提示框
excel.DisplayAlerts=false;
////设置禁止弹出覆盖的询问提示框
//excel.AlertBeforeOverwriting=false;
if(savePath!
="
){excel.ActiveWorkbook.SaveCopyAs(savePath);
else
excel.Save(AppDomain.CurrentDomain.BaseDirectory+"
bak.xls"
if(System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory+"
))System.IO.File.Delete(AppDomain.CurrentDomain.BaseDirectory+"
catch{}
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<
excelProcesses.Length;
i++)
if(startTime<
excelProcesses[i].StartTime)
startTime=excelProcesses[i].StartTime;
processId=i;
if(excelProcesses[processId].HasExited==false){excelProcesses[processId].Kill();
#region使用DataGrid输出
///使用DataGrid输出Excel文件
DataGrid"
DataGrid<
publicstaticvoidExport_DataGrid(DataGriddg){Export_DataGrid(dg,null,null,null,false);
fileName"
文件名称<
publicstaticvoidExport_DataGrid(DataGriddg,stringfileName){Export_DataGrid(dg,null,fileName,null,false);
formatCellIndex"
需要格式化的列集合,例如"
$1$2$"
为1,2列格式化为字符串<
publicstaticvoidExport_DataGrid(DataGriddg,stringfileName,stringformatCellIndex){Export_DataGrid(dg,null,fileName,formatCellIndex,false);
isWrite"
是否直接输出到前台<
Excel文件内容<
publicstaticstringExport_DataGrid(DataGriddg,stringfileName,stringformatCellIndex,boolisWrite){returnExport_DataGrid(dg,null,fileName,formatCellIndex,isWrite);
DataTable"
DataTable数据源<
publicstaticvoidExport_DataGrid(System.Data.DataTabledt){Export_DataGrid(null,dt,null,null,false);
publicstaticvoidExport_DataGrid(System.Data.DataTabledt,stringfileName){Export_DataGrid(null,dt,fileName,null,false);
publicstaticvoidExport_DataGrid(System.Data.DataTabledt,stringfileName,stringformatCellIndex){Export_DataGrid(null,dt,fileName,formatCellIndex,false);
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<
dgi.Cells.Count;
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();
//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("
response.AppendHeader("
content-disposition"
attachment;
fileName=\"
+fileName+"
\"
response.Write(oStringWriter.ToString());
response.End();
return"
#region使用GridView输出
///使用GridView输出Excel文件
GridView"
GridView<
publicstaticvoidExport_GridView(GridViewgv){Export_GridView(gv,null,null,null,false);
s