public void ExportToExcel(SystemDataDataTable dt)
{
if (dt == null) return;
ExcelApplication xlApp = new ExcelApplication();
if (xlApp == null)
{
// lblMsgText = "无法创建Excel对象，可能您的机子未安装Excel";
return;
}
ExcelWorkbooks workbooks = xlAppWorkbooks;
ExcelWorkbook workbook = workbooksAdd(ExcelXlWBATemplatexlWBATWorksheet);
ExcelWorksheet worksheet = (ExcelWorksheet)workbookWorksheets[1];//取得sheet1
ExcelRange range = null;
long totalCount = dtRowsCount;
long rowRead = 0;
float percent = 0;
//写入标题
range = worksheetget_Range(xlAppCells[1, 1], xlAppCells[1, 5]);
rangeMergeCells = true;
rangeRowHeight = 20;
rangeFontBold = true;
rangeFontSize = 10;
rangeFontColorIndex = 10;//字体颜色
xlAppActiveCellFormulaR1C1 = strSite_Name;
for (int i = 0; i < dtColumnsCount; i++)
{
worksheetCells[2, i + 1] = dtColumns[i]ColumnName;
range = (ExcelRange)worksheetCells[2, i + 1];
//rangeInteriorColorIndex = 15;//背景颜色
rangeFontBold = true;//粗体
rangeHorizontalAlignment = ExcelXlHAlignxlHAlignCenter;//居中
//加边框
rangeBorderAround(ExcelXlLineStylexlContinuous, ExcelXlBorderWeightxlThin, ExcelXlColorIndexxlColorIndexAutomatic, null);
//rangeColumnWidth = 463;//设置列宽
//rangeEntireColumnAutoFit();//自动调整列宽
//r1EntireRowAutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dtRowsCount; r++)
{
for (int i = 0; i < dtColumnsCount; i++)
{
worksheetCells[r + 3, i + 1] = dtRows[r][i];
range = (ExcelRange)worksheetCells[r + 3, i + 1];
rangeFontSize = 9;//字体大小
//加边框
rangeBorderAround(ExcelXlLineStylexlContinuous, ExcelXlBorderWeightxlThin, ExcelXlColorIndexxlColorIndexAutomatic, null);
rangeEntireColumnAutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 rowRead)) / totalCount;
SystemWindowsFormsApplicationDoEvents();
}
((ExcelRange)worksheetCells[2, 1])ColumnWidth = 25;
((ExcelRange)worksheetCells[2, 2])ColumnWidth = 25;
((ExcelRange)worksheetCells[2, 3])ColumnWidth = 20;
((ExcelRange)worksheetCells[2, 4])ColumnWidth = 20;
((ExcelRange)worksheetCells[2, 5])ColumnWidth = 60;
rangeBorders[ExcelXlBordersIndexxlInsideHorizontal]Weight = ExcelXlBorderWeightxlThin;
if (dtColumnsCount > 1)
{
rangeBorders[ExcelXlBordersIndexxlInsideVertical]Weight = ExcelXlBorderWeightxlThin;
}
try
{
workbookSaved = true;
workbookSaveCopyAs("C:\\Documents and Settings\\Administrator\\桌面\\Logxls");
MessageBoxShow("Export Successful!");
}
catch (Exception ex)
{
}
workbooksClose();
if (xlApp != null)
{
xlAppWorkbooksClose();
xlAppQuit();
int generation = SystemGCGetGeneration(xlApp);
SystemRuntimeInteropServicesMarshalReleaseComObject(xlApp);
xlApp = null;
SystemGCCollect(generation);
}
GCCollect();//强行销毁
#region 强行杀死最近打开的Excel进程
SystemDiagnosticsProcess[] excelProc = SystemDiagnosticsProcessGetProcessesByName("EXCEL");
SystemDateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProcLength; m++)
{
if (startTime < excelProc[m]StartTime)
{
startTime = excelProc[m]StartTime;
killId = m;
}
}
if (excelProc[killId]HasExited == false)
{
excelProc[killId]Kill();
}
#endregion
}
你学c#多久了？提供一个思路：生成成绩表格需要查表 *** 作，把数据读入datatable,然后用dataview显示出来即可。
excel部分比较麻烦，这有我原先临时把数据从datatable导入excel的一段代码，可以参考。
using System;
using SystemCollectionsGeneric;
using SystemText;
using SystemDataOleDb;
using SystemData;
using SystemIO;
namespace WindowsApplication1
{
class excel
{
public static string accesstoexcel(string Pathstr, DataTable dt, string name)//导出Excel表
{
object missing = SystemReflectionMissingValue;
Excel_Application Excelcontrol = new ExcelApplicationClass();
ExcelcontrolUserControl = false;
ExcelWorkbooks Excelbooks = ExcelcontrolWorkbooks;
ExcelWorkbook Excelbook = null;
ExcelWorksheet Excelsheet = null;
try
{
if (FileExists(Pathstr))
{
Excelbook = ExcelbooksAdd(Pathstr);
Excelsheet = ((ExcelWorksheet)ExcelbookWorksheetsAdd(missing, missing, missing, missing));
}
else
{
Excelbook = ExcelbooksAdd(ExcelXlWBATemplatexlWBATWorksheet);
Excelsheet = ((ExcelWorksheet)ExcelbookWorksheets[1]);
}
ExcelsheetName = name;
for (int i = 1; i < dtColumnsCount; i++)
{
ExcelsheetCells[1, i + 1] = dtColumns[i]ColumnName;
}
for (int i = 0; i < dtRowsCount; i++)
{
for (int j = 1; j < dtColumnsCount; j++)
{
if (j == 2)
{
ExcelsheetCells[i + 2, j + 1] = "'" + dtRows[i][j];
}
else
{
ExcelsheetCells[i + 2, j + 1] = dtRows[i][j];
}
}
}
ExcelbookSaveCopyAs(Pathstr);
ExcelbookSaved = true;
ExcelbookClose(missing,missing,missing);
return "导出成功！";
}
catch
{
return "导出失败！";
}
}
}
}
把datatable，存储路径作为参数传入就行了。希望对你有帮助。注意还要添加InteropExceldll和InteropOfficedll引用。祝你成功
可以的 我这有个 *** 作excel 的例子 你可以参考下
这是一个完整的 excel *** 作类 ，以前的一个项目用到的 DataSetToExcel
using System;
using SystemWindowsForms;
using SystemData;
using SystemCollections;
using SystemIO;
using SystemDiagnostics;
namespace CHUmanagerMoonCakeCommon
{
/// <summary>
/// DataSetToExcel 的摘要说明。
/// </summary>
public class DataSetToExcel
{
private DateTime beforeTime; //Excel启动之前时间
private DateTime afterTime; //Excel启动之后时间
public DataSetToExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="filename">为要保存的excel文件名</param>
///<param name="GridX">要打开的DataGrid</param>
/// <param name="excelname">excelname为excel表的标题</param>
public bool ExportToExcel(string filename,DataGrid GridX,string excelname)
{
if(GridX==null) return false;
string saveFileName="";
bool fileSaved=false;
SaveFileDialog saveDialog=new SaveFileDialog();
saveDialogDefaultExt ="xls";
saveDialogFilter="Excel文件|xls";
saveDialogFileName =filename;
saveDialogShowDialog();
saveFileName=saveDialogFileName;
if(saveFileNameIndexOf(":")<0) return false; //被点了取消
beforeTime = DateTimeNow;//Excel启动之前时间
ExcelApplication excel= new ExcelApplicationClass(); //启动Excel进程
afterTime = DateTimeNow;//Excel启动之后时间
Excel_Workbook xBk = excelWorkbooksAdd(true);
Excel_Worksheet xSt = (Excel_Worksheet)xBkActiveSheet;
ExcelRange excelCell=null;
try
{
//赋值对象
object[] objarr;
DataTable dtTest=new DataTable();;
int i,j;
int iRows,iCows;
int iVisable;
iVisable=0;
iCows=0;
ArrayList list=new ArrayList();
//如果绑定数据源是DataTable和DataSet,取得行数
if (GridXDataSource is SystemDataDataTable)
{
dtTest=(DataTable)GridXDataSource;
iRows=dtTestRowsCount;
}
else if(GridXDataSource is SystemDataDataSet)
{
DataSet ds=(DataSet)GridXDataSource;
dtTest=dsTables[0];
iRows=dtTestRowsCount;
}
else if (GridXDataSource is SystemDataDataView)
{
DataView dvTest=(DataView)GridXDataSource;
iRows=dvTestCount;
dtTest=dvTestTable;
}
//如果是集合取得行数
else
{
SystemCollectionsCollectionBase ColTest;
ColTest=(SystemCollectionsCollectionBase)GridXDataSource;
iRows=ColTestCount;
}
//添加表头
xStCells[1,1] = excelname;
//表头样式
excelCell=(ExcelRange)xStCells[1,1];
excelCellInteriorColorIndex = 15;
excelCellFontSize = 14;
excelCellFontName = "隶书";
excelCellFontBold = true;
excelCellHorizontalAlignment = ExcelXlHAlignxlHAlignCenter;
//如果有TableStyles则根据TableStyles取得(标题行)
if (GridXTableStylesCount>0)
{
listClear();
iCows=GridXTableStyles[0]GridColumnStylesCount;
for(i=0;i<iCows;i++)
{
if(GridXTableStyles[0]GridColumnStyles[i]Width>0)
{
iVisable++;
listAdd(GridXTableStyles[0]GridColumnStyles[i]HeaderText);
//获取字段样式
excelCell=(ExcelRange)xStCells[2,i+1];
excelCellFontBold = true;
}
}
objarr = new object[iVisable];
objarr=listToArray();
excelCell = xStget_Range(excelCells[2,1],excelCells[2,iVisable]);
excelCellValue2 = objarr;
//数据行
for(i=0;i<iRows;i++)
{
objarr = new object[iVisable];
listClear();
for(j=0;j<iCows;j++)
{
if(GridXTableStyles[0]GridColumnStyles[j]Width>0)
{
listAdd(GridX[i,j]ToString()Replace("\n",""));
}
}
if (listEquals(SystemDBNullValue))
{
break;
}
objarr=listToArray();
excelCell = xStget_Range(excelCells[i+3,1],excelCells[i+3,iVisable]);
excelCellValue2 = objarr;
}
}
else
{
iCows=dtTestColumnsCount;
for(i=0;i<iCows;i++)
{
listAdd(dtTestColumns[i]CaptionToString());
}
objarr = new object[iCows];
objarr=listToArray();
excelCell = xStget_Range(excelCells[2,1],excelCells[2,iCows]);
excelCellValue2 = objarr;
//数据行
for(i=0;i<iRows;i++)
{
objarr = new object[iCows];
listClear();
for(j=0;j<iCows;j++)
{
listAdd(GridX[i,j]ToString()Replace("\n",""));
}
if (listEquals(SystemDBNullValue))
{
break;
}
objarr=listToArray();
excelCell = xStget_Range(excelCells[i+3,1],excelCells[i+3,iCows]);
excelCellValue2 = objarr;
}
}
//合并单元格
ExcelRange rH = xStget_Range("A1",intToString(iCows)+"1");
rHMerge(0);
dtTestDispose();
if(saveFileName!="")
{
try
{
xBkSaved = true;
xBkSaveCopyAs(saveFileName);
fileSaved=true;
}
catch(Exception ex)
{
fileSaved=false;
MessageBoxShow("导出文件时出错,文件可能正被打开！\n"+exMessage);
}
}
else
{
fileSaved=false;
}
}
catch (SystemException e)
{
SystemWindowsFormsMessageBoxShow(eMessage);
}
finally
{
excelCell=null;
xBk=null;
xSt=null;
excel=null;
GCCollect();
KillExcelProcess();//强制结束Excel进程
}
return fileSaved;
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="filename">为要保存的excel文件名</param>
/// <param name="dt">dt为关联grid的DataTable</param>
/// <param name="excelname">excelname为excel表的标题</param>
public bool ExportToExcel(string filename,DataTable dt,string excelname)
{
if(dt==null) return false;
string saveFileName="";
bool fileSaved=false;
SaveFileDialog saveDialog=new SaveFileDialog();
saveDialogDefaultExt ="xls";
saveDialogFilter="Excel文件|xls";
saveDialogFileName =filename;
saveDialogShowDialog();
saveFileName=saveDialogFileName;
if(saveFileNameIndexOf(":")<0) return false; //被点了取消
beforeTime = DateTimeNow;//Excel启动之前时间
ExcelApplication xlApp=new ExcelApplication();
afterTime = DateTimeNow;//Excel启动之后时间
if(xlApp==null)
{
MessageBoxShow("无法创建Excel对象，可能您的机子未安装Excel");
return false;
}
ExcelWorkbooks workbooks=xlAppWorkbooks;
ExcelWorkbook workbook=workbooksAdd(ExcelXlWBATemplatexlWBATWorksheet);
ExcelWorksheet worksheet=(ExcelWorksheet)workbookWorksheets[1];//取得sheet1
ExcelRange range;
long totalCount=dtRowsCount;
long rowRead=0;
float percent=0;
//合并单元格
ExcelRange rH = worksheetget_Range("A1",intToString(dtColumnsCount)+"1");
rHMerge(0);
//写如标题
worksheetCells[1,1]=excelname;
//标题样式
range=(ExcelRange)worksheetCells[1,1];
rangeInteriorColorIndex = 15;
rangeFontSize = 14;
rangeFontName = "隶书";
rangeFontBold = true;
rangeHorizontalAlignment = ExcelXlHAlignxlHAlignCenter;
//写入字段
for(int i=0;i<dtColumnsCount;i++)
{
worksheetCells[2,i+1]=dtColumns[i]ColumnName;
//获取字段样式
range=(ExcelRange)worksheetCells[2,i+1];
rangeFontBold = true;
}
//写入数值
for(int r=0;r<dtRowsCount;r++)
{
for(int i=0;i<dtColumnsCount;i++)
{
worksheetCells[r+3,i+1]=dtRows[r][i];
}
rowRead++;
percent=((float)(100rowRead))/totalCount;
SystemWindowsForms ApplicationDoEvents();
}
range=worksheetget_Range(worksheetCells[2,1],worksheetCells[dtRowsCount+2,dtColumnsCount]);
rangeBorderAround(ExcelXlLineStylexlContinuous,ExcelXlBorderWeightxlThin,ExcelXlColorIndexxlColorIndexAutomatic,null);
rangeBorders[ExcelXlBordersIndexxlInsideHorizontal]ColorIndex = ExcelXlColorIndexxlColorIndexAutomatic;
rangeBorders[ExcelXlBordersIndexxlInsideHorizontal]LineStyle =ExcelXlLineStylexlContinuous;
rangeBorders[ExcelXlBordersIndexxlInsideHorizontal]Weight =ExcelXlBorderWeightxlThin;
if(dtColumnsCount>1)
{
rangeBorders[ExcelXlBordersIndexxlInsideVertical]ColorIndex =ExcelXlColorIndexxlColorIndexAutomatic;
rangeBorders[ExcelXlBordersIndexxlInsideVertical]LineStyle = ExcelXlLineStylexlContinuous;
rangeBorders[ExcelXlBordersIndexxlInsideVertical]Weight = ExcelXlBorderWeightxlThin;
}
if(saveFileName!="")
{
try
{
workbookSaved =true;
workbookSaveCopyAs(saveFileName);
fileSaved=true;
}
catch(Exception ex)
{
fileSaved=false;
MessageBoxShow("导出文件时出错,文件可能正被打开！\n"+exMessage);
}
}
else
{
fileSaved=false;
}
xlAppQuit();
GCCollect();//强行销毁,经测试这句话并没有结束Excel进程
KillExcelProcess();//强制结束进程
//用Excel打开文件
//if(fileSaved&&FileExists(saveFileName))SystemDiagnosticsProcessStart(saveFileName);
return fileSaved;
}
/// <summary>
/// 数字转字符。如：1转A 2转B
/// </summary>
/// <param name="num">要转换的数字,最大为26,最小为1</param>
/// <returns></returns>
private string intToString(int num)
{
if (num>26 || num<1)
{
return null;
}
string[] str = new string[26];
str[0] = "A";
str[1] = "B";
str[2] = "C";
str[3] = "D";
str[4] = "E";
str[5] = "F";
str[6] = "G";
str[7] = "H";
str[8] = "I";
str[9] = "J";
str[10] = "K";
str[11] = "L";
str[12] = "M";
str[13] = "N";
str[14] = "O";
str[15] = "P";
str[16] = "Q";
str[17] = "R";
str[18] = "S";
str[19] = "T";
str[20] = "U";
str[21] = "V";
str[22] = "W";
str[23] = "S";
str[24] = "Y";
str[25] = "Z";
return str[num-1];
}
/// <summary>
/// 结束Excel进程
/// </summary>
private void KillExcelProcess()
{
Process[] myProcesses;
DateTime startTime;
myProcesses = ProcessGetProcessesByName("Excel");
//得不到Excel进程ID，暂时只能判断进程启动时间
foreach(Process myProcess in myProcesses)
{
startTime = myProcessStartTime;
if(startTime > beforeTime && startTime < afterTime)
{
myProcessKill();
}
}
}
}
}
看完别忘记了加分啊！
以上就是关于asp.net sql数据导入excel word问题全部的内容，包括:asp.net sql数据导入excel word问题、C# 数据库和EXCEL的问题、C#能不能像 *** 作SQL数据库那样使用sql语句对excel进行读取更新等 *** 作等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）