DataTable 导出Excel VB+C# 实例源码.docx
《DataTable 导出Excel VB+C# 实例源码.docx》由会员分享,可在线阅读,更多相关《DataTable 导出Excel VB+C# 实例源码.docx(12页珍藏版)》请在冰豆网上搜索。
DataTable导出ExcelVB+C#实例源码
C#导出Excle(简化版):
publicpartialclassForm1:
Form
{
privatestaticOleDbConnectionconn=newOleDbConnection();
privatestaticOleDbCommandcomm=newOleDbCommand();
privateOleDbDataAdaptercoda;
privateDataSetcods=newDataSet();
privatestringsqlword;
privateintc;
privateListcolumnList;
publicForm1()
{
InitializeComponent();
}
privatevoidForm1_Load(objectsender,EventArgse)
{
this.dataGridView1.RowHeadersVisible=false;
this.dataGridView1.AllowUserToAddRows=false;
this.dataGridView1.AllowUserToOrderColumns=false;
this.dataGridView1.AllowUserToDeleteRows=false;
this.dataGridView1.AllowUserToResizeColumns=false;
this.dataGridView1.AllowUserToResizeRows=false;
this.dataGridView1.MultiSelect=false;
this.dataGridView1.ReadOnly=true;
this.dataGridView1.SelectionMode=DataGridViewSelectionMode.FullRowSelect;
//this.dataGridView1.AutoGenerateColumns=false;
DataColumndtColumn=newDataColumn();
System.Data.DataTabledt=newSystem.Data.DataTable();
DataSetds=newDataSet();
inti=0;
ds.ReadXml(System.IO.Path.GetFullPath("../../MyDataTable.xml"));//读取数据库绝对路径
DataRow[]drColumnToDisplay=newDataRow[ds.Tables[0].Select("visiable='true'").Length];
string[]strName=newstring[drColumnToDisplay.Length];
drColumnToDisplay=ds.Tables[0].Select("visiable='true'");//读取xml中有效的列名
columnList=newList();
foreach(DataRowdrindrColumnToDisplay)
{
dtColumn=newDataColumn();
dtColumn.ColumnName=dr["strucrtName"].ToString();
dt.Columns.Add(dtColumn);
columnList.Add(dr["strucrtName"].ToString());
strName[i]=dr["strucrtName"].ToString();
i+=1;
}
//增加数据行
System.Data.DataTabledt1=fillDT("");
for(i=0;i<=dt1.Rows.Count-1;i++)
{
DataRowdr=dt.NewRow();
for(intj=0;j<=dt1.Columns.Count-1;j++)
{
stringa=dt1.Columns[j].ColumnName.ToString();
if(caseName(dt1.Columns[j].ColumnName.ToString(),dt1,i,j)!
="")
{
dr[dt1.Columns[j].ColumnName.ToString()]=caseName(dt1.Columns[j].ColumnName.ToString(),dt1,i,j);
}
}
dt.Rows.Add(dr);
}
this.dataGridView1.DataSource=dt;
for(intk=0;k<=dt.Columns.Count-1;k++)
{
dt.Columns[k].ColumnName=columnName(dt.Columns[k].ColumnName);
}
}
publicOleDbConnectionGetSqlConnection()
{
stringdbPath=System.IO.Path.GetFullPath("../../post.mdb");
stringConnect="Provider=Microsoft.Jet.OleDb.4.0;DataSource="+dbPath+";PersistSecurityInfo=True;JetOLEDB:
DatabasePassword=smartcard";
OleDbConnectionconn=newOleDbConnection(Connect);
returnconn;
}
publicSystem.Data.DataTablefillDT(stringseekword)
{
conn=GetSqlConnection();
if(seekword.Length>0)
{seekword="and"+seekword;}
sqlword="select*fromtb_service"+seekword;
coda=newOleDbDataAdapter(sqlword,conn);
try
{
DataSetfillds=newDataSet();
coda.Fill(fillds,"list");
c=fillds.Tables["list"].Rows.Count;
GC.Collect();
returnfillds.Tables["list"];
}
catch(Exceptione)
{
System.Data.DataTabledt=newSystem.Data.DataTable();
GC.Collect();
returndt;
}
}
privatestringcaseName(stringname,System.Data.DataTabledt,inti,intj)
{
intkeyIndex=columnList.IndexOf(name);
if(keyIndex!
=-1)
{
returndt.Rows[i][dt.Columns[j].ColumnName.ToString()].ToString();
}
return"";
}
privatestringcolumnName(stringname)
{
switch(name)
{
case"service_name":
return"服务名称";
case"service_type":
return"服务类别";
case"service_price":
return"服务价格";
case"service_cycle":
return"服务时限";
case"cycle_time":
return"服务周期";
case"start_time":
return"开始时间";
case"available":
return"是否有效";
}
return"";
}
privatestring[]colName(System.Data.DataTabledt)//获取DataGridView列名
{
stringcolName="";
for(inti=0;i<=dt.Columns.Count-1;i++)
{
colName=dt.Columns[i].ColumnName.ToString()+"|"+colName;
}
colName=colName.Substring(0,colName.LastIndexOf("|"));
string[]sColName=colName.Split(newchar[]{'|'});
string[]newColName=newstring[sColName.Length];
for(intj=sColName.Length-1;j>=0;j--)
{
newColName[(sColName.Length-1)-j]=sColName[j];
}
returnnewColName;
}
privatevoidbutton1_Click(objectsender,EventArgse)
{
ExportExcel.ExcelOPep=newExcelOP();
ep.DataTableToExcel(colName((System.Data.DataTable)this.dataGridView1.DataSource),"test",(System.Data.DataTable)this.dataGridView1.DataSource,DateTime.Now.ToString("yyyy-MM-ddHH:
mm:
ss"),"test","0",0,0);
}
privatevoidbutton2_Click(objectsender,EventArgse)
{
Microsoft.Office.Interop.Excel.Applicationapp=newMicrosoft.Office.Interop.Excel.Application();
app.Visible=true;
WorkbookwBook=(Workbook)app.Workbooks.Add(true);
WorksheetwSheet=wBook.Worksheets[1]asWorksheet;
for(intk=0;k<=colName((System.Data.DataTable)this.dataGridView1.DataSource).Length-1;k++)
{
wSheet.Cells[1,k+1]=colName((System.Data.DataTable)this.dataGridView1.DataSource)[k].ToString();
}
if(((System.Data.DataTable)this.dataGridView1.DataSource).Rows.Count>0)
{
introw,col;
row=((System.Data.DataTable)this.dataGridView1.DataSource).Rows.Count;
col=((System.Data.DataTable)this.dataGridView1.DataSource).Columns.Count;
for(inti=0;i<=row-1;i++)
{
for(intj=0;j<=col-1;j++)
{
wSheet.Cells[i+2,j+1]=((System.Data.DataTable)this.dataGridView1.DataSource).Rows[i][j].ToString();
}
}
}
wSheet.Columns.AutoFit();
wSheet.Columns.Font.Name="Arial";
wSheet.Columns.Font.Size=10;
}
}
VB导出Excel:
PublicClassExcelOP
#Region"PublicDefinition"
PrivateoXLAsExcel.Application
PrivateoWBAsExcel.Workbook
PrivateoSheetAsExcel.Worksheet
PrivateoRngAsExcel.Range
PrivatetotalValueAsDouble
#EndRegion
'''
'''导出Excel
'''
'''表头
'''标题
'''数据源
'''日期
'''工作簿名字
'''需要计算的列名
'''是否需要计算
'''列所在的索引值
'''无返回值
'''
PublicFunctionDataTableToExcel(ByValCellsNameAsString(),ByValTitleAsString,ByValDTAsSystem.Data.DataTable,ByValExcelDateAsString,ByValSheetNameAsString,ByValToTalNameAsString,ByValisTotalAsInteger,ByValcolIndexAsInteger)
Dimi,jAsInteger
DimstartRowAsInteger=4
DimstartCell,endCellAsString
Try
oXL=CreateObject("Excel.Application")'createaexcelapplicationwhenwassetuptheofficeofexcel
oXL.Visible=True
oWB=oXL.Workbooks.Add
oSheet=oXL.ActiveSheet
oSheet.Name=SheetName
WithoSheet
Fori=0ToCellsName.Length-1
.Cells(startRow,i+1)=CellsName(i).ToString()
Next
EndWith
DimRcountAsInteger=DT.Rows.Count
DimCcountAsInteger=DT.Columns.Count
IfDT.Rows.Count>0Then
Fori=0ToRcount-1
Forj=0ToCcount-1
If(DT.Rows(i).Item(j).ToString().Length>10)Then
oSheet.Cells(i+startRow+1,j+1)="'"+DT.Rows(i).Item(j)
Else
oSheet.Cells(i+startRow+1,j+1)=DT.Rows(i).Item(j)
EndIf
Next
IfisTotal=1Then
Get_Total(Double.Parse(DT.Rows(i).Item(ToTalName)))
EndIf
Next
EndIf
IfisTotal=1Then
'formattheexcelcolumn
oSheet.Cells(i+6,1)="总计"
oSheet.Cells(i+6,colIndex+1)=totalValue
oSheet.Range("A"&(i+6),Get_Excel_Rang(colIndex+1)&(i+6)).Interior.ColorIndex=37
oSheet.Range("A"&(i+6),Get_Excel_Rang(colIndex+1)&(i+6)).Font.Bold=True
EndIf
startCell="A"&startRow
endCell=Get_Excel_Rang(CellsName.Length)&startRow.ToString()
oSheet.Range(startCell,endCell).Interior.ColorIndex=37
oSheet.Range(startCell,endCell).Font.Bold=True
oSheet.Range(startCell,endCell).Borders.LineStyle=Excel.XlLineStyle.xlContinuous
oSheet.Columns.Font.Name="Arial"
oSheet.Columns.Font.Size=10
oSheet.Columns.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter
oSheet.Columns.AutoFit()
oSheet.Cells(1,1)=Title
oSheet.Cells(2,1)=ExcelDate
oRng=oSheet.Range("A1","E1")
WithoRng
.Merge()
.HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft
EndWith
oRng=oSheet.Range("A2","E2")
WithoRng
.Merge()
.HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft
EndWith
'deletethesheetforexcel
oWB.Worksheets("Sheet2").Delete()
oWB.Worksheets("Sheet3").Delete()
CatchexAsException
System.Windows.Forms.MessageBox.Show(ex.Message.ToString())
EndTry
ReturnTrue
EndFunction
PrivateFunctionGet_Total(ByValValueAsDouble)'getthevalueandreturnsumofthevalue
totalValue=totalValue+Value
ReturnTrue
EndFunction
PrivateFunctionGet_Excel_Rang(ByValsheetCellsAsInteger)AsString
SelectCasesheetCells
Case1
Return"A"
ExitSelect
Case2
Return"B"
ExitSelect
Case3
Return"C"
ExitSelect
Case4
Return"D"
ExitSelect
Case5
Return"E"
ExitSelect
Case6
Return"F"
ExitSelect
Case7
Return"G"
ExitSelect
Case8
Return"H"
ExitSelect
Case9
Return"I"
ExitSelect
Case10
Return"J"
ExitSelect
Case11
Return"K"
ExitSelect
Case12
Return"L"
ExitSelect
Case13
Return"M"
ExitSelect
Case14
Return"N"
ExitSelect
Case15
Return"O"
ExitSelect
Case16
Return"P"
Case17
Return"Q"
ExitSelect
Case18
Return"R"
ExitSelect
Case19
Return"S"
ExitSelect
Case20
Return"T"
ExitSelect
Case21
Return"U"
ExitSelect
Case22
Return"V"
ExitSelect
Case23
Return"W"
ExitSelect
Case24
Return"X"
ExitSelect
Case