如何用.net把数据库里的数据生成excel文档比如每个月的工作进度数据，这些数据能用gridview显示，能不能用新
如何用.net把数据库里的数据生成excel文档
比如每个月的工作进度数据，这些数据能用gridview显示，能不能用新建一excel文档，把这些数据写进去呢？？
[解决办法]
从数据库读出来后新建一个excel文档
写入就可以了
这个很简单的
- C# code
protected void ExportOriginalData() { Stream mystream; Connection con = new Connection(); SqlConnection conn; conn = con.Create(); conn.Open(); SqlDataAdapter da = new SqlDataAdapter("select * from data", conn); DataSet ds = new DataSet(); da.Fill(ds, "mytable"); DataTable dt = ds.Tables["mytable"]; if ((mystream = saveFileDialog1.OpenFile()) != null) { System.IO.StreamWriter sw = new StreamWriter(mystream, System.Text.Encoding.Default); for (int i = 0; i < dt.Columns.Count; i++) { sw.Write(dt.Columns[i].ColumnName.ToString() + "\t"); } sw.WriteLine(); for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { sw.Write(dt.Rows[i][j].ToString().Replace("\r\n", "").Replace("\t", "") + "\t"); } sw.WriteLine(); } sw.Close(); mystream.Close(); } ChangeStatus(false); }
[解决办法]
http://www.cnblogs.com/dengxinghong/archive/2008/04/04/1137853.html
[解决办法]
C#,GridView导出到Excel
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="title"></param>
public static void DataGridViewToExcel(DataGridView dgv, string title)
{
if (dgv.DataSource == null || dgv.Columns.Count == 0)
{
return;
}
Excel.Application exc = new Excel.ApplicationClass();
if (exc == null)
{
throw new Exception("Excel无法启动");
}
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = exc.Sheets;
_Worksheet worksheet = (_Worksheet)sheets[1];
if (worksheet == null)
{
throw new Exception("Worksheet error");
}
DataGridViewClipboardCopyMode bakMode = dgv.ClipboardCopyMode;
bool copyCurrentCellText = true;
bool showRowHeader = dgv.RowHeadersVisible;
if (dgv is gvGridView)
{
copyCurrentCellText = ((gvGridView)dgv).CopyCurrentCellText;
((gvGridView)dgv).CopyCurrentCellText = false;
}
dgv.RowHeadersVisible = false;
IDataObject bakClipbordDasta = Clipboard.GetDataObject();
dgv.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
IDataObject exportData = dgv.GetClipboardContent();
if (exportData != null)
{
DataObject data = exportData as DataObject;
Clipboard.SetDataObject(data.GetText(TextDataFormat.UnicodeText));
}
if (dgv is gvGridView)
{
((gvGridView)dgv).CopyCurrentCellText = copyCurrentCellText;
}
dgv.RowHeadersVisible = showRowHeader;
if (exportData != null)
{
exc.Columns.NumberFormatLocal = "@";
worksheet.Paste(worksheet.get_Range("A2", System.Type.Missing), false);
}
Clipboard.SetDataObject(bakClipbordDasta);
Range r = worksheet.get_Range(exc.Cells[1, 1], exc.Cells[1, dgv.Columns.Count]);
exc.Visible = false;
r.MergeCells = true;
if (r == null)
{
MessageBox.Show("Range无法启动");
throw new Exception("Range error");
}
//以上是一些例行的初始化工作,下面进行具体的信息填充
//标题
exc.ActiveCell.FormulaR1C1 = title;
exc.ActiveCell.Font.Size = 12;
exc.ActiveCell.Font.Bold = true;
exc.Cells.EntireColumn.AutoFit();
//加边框
exc.Cells.EntireColumn.Borders.LineStyle = 1;
exc.Cells.EntireColumn.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;
exc.Cells.EntireColumn.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;
exc.Cells.EntireColumn.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;
exc.Cells.EntireColumn.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
exc.Cells.VerticalAlignment = Excel.Constants.xlCenter;
exc.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
exc.Visible = true;
}
[解决办法]
web的吗?
调用下面方法,传一个datatable,和当前页面的对象就去就OK了,会提示让用户下载到客户端
如果在当前的(xx.aspx.cs中调用第二个参数传this即可)
- C# code
public void CreatExcel(System.Data.DataTable dt,System.Web.UI.Page thisPage) { StringWriter sw = new StringWriter(); string rowStr = ""; //取所有列名 for (int i = 0; i < dt.Columns.Count; i++) { rowStr = rowStr + dt.Columns[i] + "\t"; } sw.WriteLine(rowStr); //取每行数据 for (int j = 0; j < dt.Rows.Count; j++) { rowStr = ""; for (int i = 0; i < dt.Columns.Count; i++) { rowStr = rowStr + dt.Rows[j][i].ToString() + "\t"; } sw.WriteLine(rowStr); } sw.Close(); string filename = DateTime.Now.ToString("yyyyMMddhhmmss"); //System.Web.UI.Page pp= new Page(); thisPage.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); thisPage.Response.ContentType = "application/ms-excel"; thisPage.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); thisPage.Response.Write(sw); thisPage.Response.End(); }
[解决办法]
- C# code
{ Microsoft.Office.Interop.Excel.Application xlApp = default(Microsoft.Office.Interop.Excel.Application); xlApp = (Microsoft.Office.Interop.Excel.Application)Interaction.CreateObject("Excel.Application"); Microsoft.Office.Interop.Excel.Workbook xlBook = default(Microsoft.Office.Interop.Excel.Workbook); xlBook = xlApp.Workbooks.Open("D:\\Test"); Microsoft.Office.Interop.Excel.Worksheet xlSheet = default(Microsoft.Office.Interop.Excel.Worksheet); xlSheet = xlBook.Worksheets("Sheet1"); Conn.Open(); SqlDataReader Rs = null; SqlCommand Command = new SqlCommand(); Command.Connection = Conn; Command.CommandText = "Select * from MjData"; Command.CommandType = CommandType.Text; Command.ExecuteNonQuery(); Rs = Command.ExecuteReader; if (Rs.Read) { bool RsRead = true; string Number = null; int i = 0; i = 2; while (RsRead) { Number = "e" + (string)i; xlSheet.Range(Number).Value = Rs("Color"); RsRead = Rs.Read; i = i + 1; } } xlBook.Save(); xlBook.Close(); xlApp.Workbooks.Close(); xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlBook = null; xlApp = null; GC.Collect(); }
[解决办法]
楼上的代码都给了,我这接点分吧