winform 与excel 在C#中,怎样将sql数据库里的数据放到excel表格中 ,并可以编辑每个单元格 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 public void ExportDataGridToExcelRang(DataGrid grid,string FileName) { System.Data.DataTable DT = new System.Data.DataTable(); System.Data.DataTable myTable = new System.Data.DataTable (); DT=(System.Data.DataTable)grid.DataSource; if(DT.DefaultView.Count>65636) { System.Windows.Forms.DialogResult _Result = MessageBox.Show("导出数据条数超过EXCEL文件65536行的最大限制,是否导出CSV文件?","系统提示",System.Windows.Forms.MessageBoxButtons.OKCancel); if (_Result == System.Windows.Forms.DialogResult.OK) { FileName.Replace( ".CSV",".CSV"); this.ExportDataGridToWindowsCSV(DT,FileName); return; } else { return; } } //仅仅导出看到的数据 myTable = DT.DefaultView.Table .Clone (); for (int i=0; i < DT.DefaultView.Count; i++) { myTable.ImportRow( DT.DefaultView[i].Row); } /////////////////////////////////// try { Excel.Application xlApp = new Excel.Application (); Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook.Worksheets[1]; //Excel.Worksheet worksheet2 = (Excel.Worksheet)workbook.Worksheets[2]; //worksheet1.Name="Sheet01"; //worksheet2.Name="Sheet02"; Excel.Range range; //xlApp.Visible=true; xlApp.UserControl=false; int rowIndex; int colIndex; rowIndex = 0; colIndex = 0; if (grid.TableStyles.Count >0 ) { foreach(DataGridColumnStyle colu in grid.TableStyles[0].GridColumnStyles) { if( colu.Width>0 ) { colIndex=colIndex +1; xlApp.Cells[1,colIndex] = colu.HeaderText ; } } //range=worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[myTable.Rows.Count+1,myTable.Columns.Count]); int ColumnStylesCount = grid.TableStyles[0].GridColumnStyles.Count; range=worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[myTable.Rows.Count+1,ColumnStylesCount]); range.Font.Name ="MS Pゴシック"; range.Font.Size =10; range.Interior.ColorIndex=35; //得到的表所有行,赋值给单元格 for (int row = 0;row < myTable.Rows.Count;row++) { rowIndex = rowIndex + 1; colIndex = 0; int colCount = grid.TableStyles[0].GridColumnStyles.Count; for (int col=0;col <colCount;col++) { if( grid.TableStyles[0].GridColumnStyles[col].Width>0) { colIndex = colIndex+1; string colName=grid.TableStyles[0].GridColumnStyles[col].MappingName; //range[rowIndex, colIndex] = grid[row,col].ToString(); //change by lidawei 2007-11-20 range[rowIndex, colIndex] = myTable.Rows[row][colName].ToString(); //range[rowIndex, colIndex] = myTable.Rows[row][col].ToString(); } } } } else { for (int p = 0 ;p <myTable.Columns.Count;p++) { xlApp.Cells[1, p+1] =DT.Columns[p].ColumnName ; } range=worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[myTable.Rows.Count+1,myTable.Columns.Count]); range.Font.Name ="MS Pゴシック"; range.Font.Size =10; for (int row = 0;row < myTable.Rows.Count;row++) { rowIndex = rowIndex + 1; colIndex = 0; int colCount = myTable.Columns.Count;//grid.TableStyles[0].GridColumnStyles.Count; for (int col=0;col <colCount;col++) { colIndex = colIndex+1; //range[rowIndex, colIndex] = grid[row,col].ToString(); //change by lidawei 2007-11-20 range[rowIndex, colIndex] = myTable.Rows[row][col].ToString(); } } } if (grid.TableStyles.Count >0 ) { range = worksheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,grid.TableStyles[0].GridColumnStyles.Count]); } else { range = worksheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,myTable.Columns.Count]); } range.Interior.ColorIndex=20; range.Font.Name ="MS Pゴシック"; range.Font.Size =10; xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colIndex]).Font.Bold = true; xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowIndex+1, colIndex]).Borders.LineStyle = 1; xlApp.Cells.EntireColumn.AutoFit(); // xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter ; // xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter ; //xlApp.AlertBeforeOverwriting=true; if(File.Exists(FileName)==true) { File.Delete(FileName); } workbook.SaveAs(FileName,miss,miss,miss,miss,miss,Excel.XlSaveAsAccessMode.xlExclusive,miss,miss,miss,miss,miss); //xlApp.Visible = true; range=null; worksheet=null; workbook=null; workbooks=null; xlApp.Quit(); xlApp=null; GC.Collect(); } catch(Exception e) { throw e; } } visual studio中怎样使用timer C# WINFORM 默认打印机问题! 什么时候用静态方法?? 请问有办法用程序获取windows所有帐户名吗? 求过滤文本框中相同内容的算法 c# winform刷新(重新加载控件)。很奇怪的问题。 一个C# WINDOWS程序中需计算5次值的平均值,统计计算次数部分不知道如何编写??? 如何用C#解压exe文件 改变字体大小用什么语句??font.size怎么无法赋值.... 事件问题 谁能简述一个C#实际案例? 数据库添加语句
{
System.Data.DataTable DT = new System.Data.DataTable();
System.Data.DataTable myTable = new System.Data.DataTable ();
DT=(System.Data.DataTable)grid.DataSource; if(DT.DefaultView.Count>65636)
{
System.Windows.Forms.DialogResult _Result = MessageBox.Show("导出数据条数超过EXCEL文件65536行的最大限制,是否导出CSV文件?","系统提示",System.Windows.Forms.MessageBoxButtons.OKCancel); if (_Result == System.Windows.Forms.DialogResult.OK)
{
FileName.Replace( ".CSV",".CSV");
this.ExportDataGridToWindowsCSV(DT,FileName);
return;
}
else
{
return;
}
} //仅仅导出看到的数据
myTable = DT.DefaultView.Table .Clone ();
for (int i=0; i < DT.DefaultView.Count; i++)
{
myTable.ImportRow( DT.DefaultView[i].Row);
}
///////////////////////////////////
try
{
Excel.Application xlApp = new Excel.Application ();
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
//Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook.Worksheets[1];
//Excel.Worksheet worksheet2 = (Excel.Worksheet)workbook.Worksheets[2];
//worksheet1.Name="Sheet01";
//worksheet2.Name="Sheet02";
Excel.Range range; //xlApp.Visible=true;
xlApp.UserControl=false; int rowIndex;
int colIndex; rowIndex = 0;
colIndex = 0; if (grid.TableStyles.Count >0 )
{
foreach(DataGridColumnStyle colu in grid.TableStyles[0].GridColumnStyles)
{
if( colu.Width>0 )
{
colIndex=colIndex +1;
xlApp.Cells[1,colIndex] = colu.HeaderText ;
}
} //range=worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[myTable.Rows.Count+1,myTable.Columns.Count]);
int ColumnStylesCount = grid.TableStyles[0].GridColumnStyles.Count;
range=worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[myTable.Rows.Count+1,ColumnStylesCount]); range.Font.Name ="MS Pゴシック";
range.Font.Size =10;
range.Interior.ColorIndex=35;
//得到的表所有行,赋值给单元格
for (int row = 0;row < myTable.Rows.Count;row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
int colCount = grid.TableStyles[0].GridColumnStyles.Count;
for (int col=0;col <colCount;col++)
{
if( grid.TableStyles[0].GridColumnStyles[col].Width>0)
{
colIndex = colIndex+1;
string colName=grid.TableStyles[0].GridColumnStyles[col].MappingName; //range[rowIndex, colIndex] = grid[row,col].ToString();
//change by lidawei 2007-11-20
range[rowIndex, colIndex] = myTable.Rows[row][colName].ToString();
//range[rowIndex, colIndex] = myTable.Rows[row][col].ToString();
}
}
}
}
else
{
for (int p = 0 ;p <myTable.Columns.Count;p++)
{
xlApp.Cells[1, p+1] =DT.Columns[p].ColumnName ;
} range=worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[myTable.Rows.Count+1,myTable.Columns.Count]); range.Font.Name ="MS Pゴシック";
range.Font.Size =10; for (int row = 0;row < myTable.Rows.Count;row++)
{
rowIndex = rowIndex + 1;
colIndex = 0; int colCount = myTable.Columns.Count;//grid.TableStyles[0].GridColumnStyles.Count;
for (int col=0;col <colCount;col++)
{
colIndex = colIndex+1;
//range[rowIndex, colIndex] = grid[row,col].ToString();
//change by lidawei 2007-11-20
range[rowIndex, colIndex] = myTable.Rows[row][col].ToString();
}
}
} if (grid.TableStyles.Count >0 )
{
range = worksheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,grid.TableStyles[0].GridColumnStyles.Count]);
}
else
{
range = worksheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,myTable.Columns.Count]);
}
range.Interior.ColorIndex=20;
range.Font.Name ="MS Pゴシック";
range.Font.Size =10; xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowIndex+1, colIndex]).Borders.LineStyle = 1; xlApp.Cells.EntireColumn.AutoFit();
// xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter ;
// xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter ; //xlApp.AlertBeforeOverwriting=true;
if(File.Exists(FileName)==true)
{
File.Delete(FileName);
} workbook.SaveAs(FileName,miss,miss,miss,miss,miss,Excel.XlSaveAsAccessMode.xlExclusive,miss,miss,miss,miss,miss); //xlApp.Visible = true; range=null;
worksheet=null;
workbook=null;
workbooks=null;
xlApp.Quit();
xlApp=null;
GC.Collect();
}
catch(Exception e)
{
throw e;
}
}