DataTable 导出Excel VB+C# 实例源码Word下载.docx
- 文档编号:16384059
- 上传时间:2022-11-23
- 格式:DOCX
- 页数:12
- 大小:17.34KB
DataTable 导出Excel VB+C# 实例源码Word下载.docx
《DataTable 导出Excel VB+C# 实例源码Word下载.docx》由会员分享,可在线阅读,更多相关《DataTable 导出Excel VB+C# 实例源码Word下载.docx(12页珍藏版)》请在冰豆网上搜索。
//读取数据库绝对路径
DataRow[]drColumnToDisplay=newDataRow[ds.Tables[0].Select("
visiable='
true'
"
).Length];
string[]strName=newstring[drColumnToDisplay.Length];
drColumnToDisplay=ds.Tables[0].Select("
);
//读取xml中有效的列名
columnList=newList<
();
foreach(DataRowdrindrColumnToDisplay)
dtColumn=newDataColumn();
dtColumn.ColumnName=dr["
strucrtName"
].ToString();
dt.Columns.Add(dtColumn);
columnList.Add(dr["
].ToString());
strName[i]=dr["
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"
coda=newOleDbDataAdapter(sqlword,conn);
try
DataSetfillds=newDataSet();
coda.Fill(fillds,"
list"
c=fillds.Tables["
].Rows.Count;
GC.Collect();
returnfillds.Tables["
];
catch(Exceptione)
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"
:
服务名称"
service_type"
服务类别"
service_price"
服务价格"
service_cycle"
服务时限"
cycle_time"
服务周期"
start_time"
开始时间"
available"
是否有效"
privatestring[]colName(System.Data.DataTabledt)//获取DataGridView列名
stringcolName="
for(inti=0;
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"
),"
"
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;
=colName((System.Data.DataTable)this.dataGridView1.DataSource).Length-1;
wSheet.Cells[1,k+1]=colName((System.Data.DataTable)this.dataGridView1.DataSource)[k].ToString();
if(((System.Data.DataTable)this.dataGridView1.DataSource).Rows.Count>
introw,col;
row=((System.Data.DataTable)this.dataGridView1.DataSource).Rows.Count;
col=((System.Data.DataTable)this.dataGridView1.DataSource).Columns.Count;
=row-1;
=col-1;
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
'
'
<
summary>
导出Excel
/summary>
paramname="
CellsName"
>
表头<
/param>
Title"
标题<
DT"
数据源<
ExcelDate"
日期<
SheetName"
工作簿名字<
ToTalName"
需要计算的列名<
isTotal"
是否需要计算<
colIndex"
列所在的索引值<
returns>
无返回值<
/returns>
remarks>
<
/remarks>
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
IfisTotal=1Then
Get_Total(Double.Parse(DT.Rows(i).Item(ToTalName)))
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
(i+6)).Font.Bold=True
startCell="
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="
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
A2"
E2"
deletethesheetforexcel
oWB.Worksheets("
Sheet2"
).Delete()
Sheet3"
CatchexAsException
System.Windows.Forms.MessageBox.Show(ex.Message.ToString())
EndTry
ReturnTrue
EndFunction
PrivateFunctionGet_Total(ByValValueAsDouble)'
getthevalueandreturnsumofthevalue
totalValue=totalValue+Value
PrivateFunctionGet_Excel_Rang(ByValsheetCellsAsInteger)AsString
SelectCasesheetCells
Case1
Return"
ExitSelect
Case2
B"
Case3
C"
Case4
D"
Case5
E"
Case6
F"
Case7
G"
Case8
H"
Case9
I"
Case10
J"
Case11
K"
Case12
L"
Case13
M"
Case14
N"
Case15
O"
Case16
P"
Case17
Q"
Case18
R"
Case19
S"
Case20
T"
Case21
U"
Case22
V"
Case23
W"
Case24
X"
Case
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DataTable 导出Excel VB+C# 实例源码 导出 Excel VB C# 实例 源码