|
openFileDialog1.ShowDialog(); oledbconn1.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source="+openFileDialog1.FileName+";Extended Properties=Excel 8.0;Persist Security Info=False"; oledbconn1.Open(); DataTable dt = oledbconn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new objec] {null, null, null, "TABLE"}); foreach (DataRow dr in dt.Rows) { comboBox1.Items.Add((String)dr["TABLE_NAME"]); } 用c#.net读取excel内容代码： string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c://book1.xls;Extended Properties=Excel 8.0" ; OleDbConnection myConn = new OleDbConnection ( strCon ) ; Excel.Application workbook = new Excel.WorkbookClass(); string strCom = " SELECT * FROM [Sheet1$] " ; myConn.Open ( ) ; OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ; DataSet myDataSet = new DataSet ( ) ; myCommand.Fill ( myDataSet , "[Sheet1$]" ); myConn.Close ( ) ; dataGrid1.DataMember= "[Sheet1$]" ; dataGrid1.DataSource = myDataSet ;
网上的一段代码： /// <summary> /// 将dataview的数据导出为报表 /// </summary> /// <param name="dv">要导出的数据</param> /// <param name="str">导出报表的标题</param> public void OutputExcel(DataView dv,string str) { // // TODO: 在此处添加构造函数逻辑 // Excel.Application excel; int rowIndex=4; int colIndex=1; Excel._Workbook xBk; Excel._Worksheet xSt; excel= new Excel.ApplicationClass(); xBk = excel.Workbooks.Add(true); xSt = (Excel._Worksheet)xBk.ActiveSheet; // //取得标题 // foreach(DataColumn col in dv.Table.Columns) { colIndex++; excel.Cells[4,colIndex] = col.ColumnName; xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐 } // //取得表格中的数据 // foreach(DataRowView row in dv) { rowIndex ++; colIndex = 1; foreach(DataColumn col in dv.Table.Columns) { colIndex ++; if(col.DataType == System.Type.GetType("System.DateTime")) { excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐 } else if(col.DataType == System.Type.GetType("System.String")) { excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString(); xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐 } else { excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString(); } } } // //加载一个合计行 // int rowSum = rowIndex + 1; int colSum = 2; excel.Cells[rowSum,2] = "合计"; xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // //设置选中的部分的颜色 // xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select(); xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种 // //取得整个报表的标题 // excel.Cells[2,2] = str; // //设置整个报表的标题格式 // xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true; xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22; // //设置报表表格为最适应宽度 // xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select(); xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit(); // //设置整个报表的标题为跨列居中 // xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select(); xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; // //绘制边框 // xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1; xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗 xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗 xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗 xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗 // //显示效果 // excel.Visible=true; } |
|
//引入Excel的COM组件 using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using Microsoft.Office.Interop; using Microsoft.Office.Core; namespace ExcelEdit { /// <summary> /// ExcelEdit 的摘要说明 /// </summary> public class ExcelEdit { public string mFilename; public Excel.Application app; public Excel.Workbooks wbs; public Excel.Workbook wb; public Excel.Worksheets wss; public Excel.Worksheet ws; public ExcelEdit() { // // TODO: 在此处添加构造函数逻辑 // } public void Create()//创建一个Excel对象 { app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(true); } public void Open(string FileName)//打开一个Excel文件 { app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); //wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "/t", false, false, 0, true,Type.Missing,Type.Missing); //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); mFilename = FileName; } public Excel.Worksheet GetSheet(string SheetName)//获取一个工作表 { Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[SheetName]; return s; } public Excel.Worksheet AddSheet(string SheetName)//添加一个工作表 { Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing); s.Name = SheetName; return s; } public void DelSheet(string SheetName)//删除一个工作表 { ((Excel.Worksheet)wb.Worksheets[SheetName]).Delete(); } public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一 { Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[OldSheetName]; s.Name = NewSheetName; return s; } public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二 { Sheet.Name = NewSheetName; return Sheet; } public void SetCellValue(Excel.Worksheet ws, int x, int y, object value)//ws：要设值的工作表 X行Y列 value 值 { ws.Cells[x, y] = value; } public void SetCellValue(string ws, int x, int y, object value)//ws：要设值的工作表的名称 X行Y列 value 值 { GetSheet(ws).Cells[x, y] = value; } public void SetCellProperty(Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)//设置一个单元格的属性 字体， 大小，颜色 ，对齐方式 { name = "宋体"; size = 12; color = Excel.Constants.xlAutomatic; HorizontalAlignment = Excel.Constants.xlRight; ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name; ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size; ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color; ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment; } public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment) { //name = "宋体"; //size = 12; //color = Excel.Constants.xlAutomatic; //HorizontalAlignment = Excel.Constants.xlRight; Excel.Worksheet ws = GetSheet(wsn); ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name; ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size; ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color; ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment; } public void UniteCells(Excel.Worksheet ws, int x1, int y1, int x2, int y2)//合并单元格 { ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing); } public void UniteCells(string ws, int x1, int y1, int x2, int y2)//合并单元格 { GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing); } public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一 { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { GetSheet(ws).Cells[startX+i, j + startY] = dt.Rows[i][j].ToString(); } } } public void InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置二 { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { ws.Cells[startX+i, j + startY] = dt.Rows[i][j]; } } } public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置一 { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j]; } } } public void AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置二 { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { ws.Cells[i + startX, j + startY] = dt.Rows[i][j]; } } } public void InsertPictures(string Filename, string ws)//插入图片操作一 { GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse,MsoTriState.msoTrue, 10, 10, 150, 150);//后面的数字表示位置 } //public void InsertPictures(string Filename, string ws, int Height, int Width)//插入图片操作二 //{ // GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150); // GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height; // GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width; //} //public void InsertPictures(string Filename, string ws, int left, int top, int Height, int Width)//插入图片操作三 //{ // GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150); // GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left); // GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top); // GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height; // GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width; //} public void InsertActiveChart(Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Excel.XlRowCol ChartDataType)//插入图表操作 { ChartDataType = Excel.XlRowCol.xlColumns; wb.Charts.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing); { wb.ActiveChart.ChartType = ChartType; wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType); wb.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws); } } public bool Save()//保存文档 { if (mFilename == "") { return false; } else { try { wb.Save(); return true; } catch (Exception ex) { return false; } } } public bool SaveAs(object FileName)//文档另存为 { try { wb.SaveAs(FileName,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return true; } catch (Exception ex) { return false; } } public void Close()//关闭一个Excel对象，销毁对象 { //wb.Save(); wb.Close(Type.Missing,Type.Missing,Type.Missing); wbs.Close(); app.Quit(); wb = null; wbs = null; app = null; GC.Collect(); } } } |
|
//获取数据DataSet/DataTable/List<>/IList protected DataSet GetList() { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=.;database=MyExample;uid=sa;pwd=123"; SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM AdminUsers", conn); //DataTable tb = new DataTable(); DataSet ds = new DataSet(); sda.Fill(ds); return ds; } //导出 Excel 只能按查询出来的数据导出，标题也没有修改 protected void Button1_Click(object sender, EventArgs e) { Response.Clear(); Response.Buffer = true; Response.AddHeader("content-disposition", "attachment; filename=Book" + DateTime.Now.ToString("yyyyMMdd") + ".xls"); Response.ContentType = "application/vnd.ms-excel"; //Response.ContentType = "application/vnd.ms-word"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.Charset = ""; this.EnableViewState = false; StringWriter oStringWriter = new StringWriter(); HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter); DataGrid dg = new DataGrid(); dg.DataSource = GetList(); //数据源DataSet/DataTable/List<>/IList dg.DataBind(); dg.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); 导出整个页面到Excel/Word //Response.Clear(); //Response.Buffer = true; //Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMdd") + ".doc"); //Response.ContentEncoding = System.Text.Encoding.UTF7; Response.ContentType = "application/vnd.ms-excel"; Response.ContentType = "application/vnd.ms-word"; //Response.ContentType = "application/word"; //this.EnableViewState = false; } |
扫一扫
9397
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
