111,110
社区成员
发帖
与我相关
我的任务
分享c#导出EXCEL表格
再做一个项目，需要从数据库（SQL SERVER 2005）里读出数据，然后生成EXCEL报表。EXCEL报表的第一行列名很复杂，有合并单元格的等等要求，请问下有没有什么好点的有详细注释的教程链接给下，在网上搜了很久很多都是没有注释的，看不懂。
或者能给我写个简单的列子（要注释）也可以，谢谢了。
或者能给我写个简单的列子（要注释）也可以，谢谢了。
...全文
请发表友善的回复…
发表回复
行侠者 2013-07-31
- 打赏
- 举报
我之前做过C#导出Excel，还可以导出图形到Excel内
http://download.csdn.net/detail/jx_521/5819725
可以指定导出哪些列、可以设置Excel列宽、标题名、列名
我是用aspose.cells.dll做的，希望可以帮助你。
Sb梁先生 2013-07-31
- 打赏
- 举报
导出excel很是麻烦，没有必要自己写。直接用NPOI就可以了哈。功能很强大的
zhao_zps 2011-11-01
- 打赏
- 举报
#region//把datagridview中的数据全部导出到EXCEL
/// <summary>
/// 把datagridview中的数据全部导出到EXCEL
/// </summary>
/// <param name="dg1"></param>
public static void CopyToExcel(DataGridView dg1)
{
dg1.SelectAll();
Application.DoEvents();
Clipboard.SetText(dg1.GetClipboardContent().GetData(DataFormats.Text).ToString());
object oMissing = System.Reflection.Missing.Value;
try
{
Excel._Worksheet Sht;
Excel._Workbook Bo;
Excel.Application excel = new Excel.Application();
Bo = excel.Application.Workbooks.Add(true);
Sht = (Excel.Worksheet)Bo.Sheets[1];
Sht.Paste(oMissing, oMissing);
excel.Visible = true;
excel = null;
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
}
}
#endregion
lihuinihao6315 2011-11-01
- 打赏
- 举报
using Microsoft.Office.Tools.Excel;private bool toExcel(DataSet ds, string path, string strTime)
{
try
{
Excel.Application excel = new Excel.Application();
// Adds a new workbook to the excel application.
Excel.Workbook book = excel.Workbooks.Add(Type.Missing);
Excel.Worksheet sheet;
if(ds.Tables[0].Rows.Count <= 0 && ds.Tables[1].Rows.Count <=0 && ds.Tables[2].Rows.Count<=0)
{
return false;
}
// Excel.Worksheet xlSheet1, xlSheet2, xlSheet3;
logger.Info("Start Function ToExcel.GetGroupDB: Start Time:" + DateTime.Now.ToString());
#region 1.群組-團體
sheet = (Excel.Worksheet)book.Worksheets.get_Item(1);
sheet.Name = "群組-團體";
int iRow = ds.Tables[0].Rows.Count+1;
int iCol = ds.Tables[0].Columns.Count;
string[,] strAll = new string[iRow, iCol];
for (int col = 0; col < ds.Tables[0].Columns.Count; col++)
{
strAll[0, col ] = ds.Tables[0].Columns[col].ColumnName;
}
for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
{
for (int col = 0; col < ds.Tables[0].Columns.Count; col++)
{
strAll[row + 1, col] = ds.Tables[0].Rows[row][col].ToString();
//sheet.Cells[row + 2, col + 1] = ds.Tables[0].Rows[row][col].ToString();
}
}
string b = "B" + iRow.ToString();
Excel.Range rng = sheet.get_Range("A1", b);
rng.Value2 = strAll;
#endregion
logger.Info("Start Function ToExcel.GetPayPeoPleDB: Start Time:" + DateTime.Now.ToString());
#region 2.繳款人
sheet = (Excel.Worksheet)book.Worksheets.get_Item(2);
sheet.Name = "繳款人";
string strCarSit = "";
DataSet dsGetOutCarSit = GetOutSellCarSit();
iRow = ds.Tables[1].Rows.Count+1 + dsGetOutCarSit.Tables[0].Rows.Count;
strAll = new string[iRow, 6];
strAll[0, 0] = "群組團體代號";//第一列列名
strAll[0, 1] = "繳款人代號"; //第二列列名
strAll[0, 2] = "繳款人名稱"; //第三列列名
strAll[0, 3] = "郵遞區號"; //第四列列名
strAll[0, 4] = "坪數"; //第五列列名
strAll[0, 5] = "車位"; //第六列列名
int iCount = ds.Tables[1].Rows.Count;
for (int row = 0; row < iCount; row++)
{
//strCarSit = GetDsWithCarSit(ds.Tables[1].Rows[row][1].ToString());
strAll[row + 1, 0] = ds.Tables[1].Rows[row][0].ToString();
strAll[row + 1, 1] = ds.Tables[1].Rows[row][1].ToString();
strAll[row + 1, 2] = ds.Tables[1].Rows[row][2].ToString();
strAll[row + 1, 3] = "'" + strTime;
strAll[row + 1, 4] = ds.Tables[1].Rows[row][3].ToString();
strAll[row + 1, 5] = ds.Tables[1].Rows[row][4].ToString();
}
for (int row = iCount; row < iCount + dsGetOutCarSit.Tables[0].Rows.Count; row++)
{
strAll[row , 0] = dsGetOutCarSit.Tables[0].Rows[row - iCount][0].ToString();
strAll[row , 1] = dsGetOutCarSit.Tables[0].Rows[row - iCount][1].ToString();
strAll[row, 2] = dsGetOutCarSit.Tables[0].Rows[row - iCount][2].ToString();
strAll[row, 3] = "'" + strTime;
strAll[row , 4] = "";
strAll[row , 5] = dsGetOutCarSit.Tables[0].Rows[row - iCount][3].ToString();
}
dsGetOutCarSit.Clear();
b = "F" + iRow.ToString();
rng = sheet.get_Range("A1", b);
rng.Value2 = strAll;
#endregion
logger.Info("Start Function ToExcel.GetPayPeoPleDB: Start Time:" + DateTime.Now.ToString());
#region 3.繳費資料
sheet = (Excel.Worksheet)book.Worksheets.get_Item(3);
sheet.Name = "繳費資料";
DataSet dsSupply = GetApplySheet();
iRow = ds.Tables[2].Rows.Count+1 + dsSupply.Tables[0].Rows.Count;
strAll = new string[iRow, 7];
strAll[0, 0] = "群組團體代號"; //第一列列名
strAll[0, 1] = "繳款人代號"; //第二列列名
strAll[0, 2] = ddl_Month.SelectedValue.ToString() + "月社區管理費"; //第三列列名
strAll[0, 3] = ddl_Month.SelectedValue.ToString() + "月汽車停車位管理費"; //第四列列名
strAll[0, 4] = ddl_Month.SelectedValue.ToString() + "月機車停車位管理費"; //第五列列名
strAll[0, 5] = ddl_Month.SelectedValue.ToString() + "月其他"; //第六列列名
strAll[0, 6] = "總金額"; //第七列列名
for (int row = 0; row < ds.Tables[2].Rows.Count; row++)
{
strAll[row + 1, 0] = ds.Tables[2].Rows[row][0].ToString();
strAll[row + 1, 1] = ds.Tables[2].Rows[row][1].ToString();
strAll[row + 1, 2] = ds.Tables[2].Rows[row][2].ToString();
strAll[row + 1, 3] = ds.Tables[2].Rows[row][3].ToString();
strAll[row + 1, 4] = ds.Tables[2].Rows[row][4].ToString();
strAll[row + 1, 5] = ds.Tables[2].Rows[row][5].ToString();
strAll[row + 1, 6] = ds.Tables[2].Rows[row][6].ToString();
}
iCount = ds.Tables[2].Rows.Count;
for (int row = 0; row < dsSupply.Tables[0].Rows.Count; row++)
{
for (int col = 0; col < dsSupply.Tables[0].Columns.Count; col++)
{
strAll[row + iCount , col] = dsSupply.Tables[0].Rows[row][col].ToString();
}
}
dsSupply.Clear();
iRow++;
b = "G" + iRow.ToString();
rng = sheet.get_Range("A1", b);
rng.Value2 = strAll;
#endregion
logger.Info("Start Function ToExcel.GetPayPeoPleDB: End Time:" + DateTime.Now.ToString());
string strServerPath = System.Web.Configuration.WebConfigurationManager.AppSettings["uploadpath"] + "JWNQ010\\";
if (!System.IO.Directory.Exists(strServerPath))
{
System.IO.Directory.CreateDirectory(strServerPath);
}
ds.Clear();
book.Close(true, path, Type.Missing);
// Exit excel application.
excel.Quit();
GC.Collect();
return true;
}
catch (Exception err)
{
logger.ErrorException("JWNQ010 ToExcel-New EXCEL：[" + err.Message.ToString() + "]", err);
return false;
}
}
使用C#实现了excel表格的导出功能，包含xls和xlsx两种格式。
程序有添加关于NOPI的引用，有疑问可以参考我的csdn博客http://blog.csdn.net/my_clear_mind
