推荐两款三方插件:aspose,NPOI,如不懂在咨询我
解决方案 »
- 有没有用dotnetskin的?
- 一个关于把程序生成唯一exe的问题
- 派生类重载与派生类重写有什么区别。
- 讨论!SocketAsyncEventArgs高性能网络类!
- 界面中的控件对应C#中???控件
- 异常:No error message available, result code: E_FAIL(0x80004005).
- 父窗口和子窗口(在线等!必给分)
- winform中的datagridview 的一列绑定autocommpletesource 的问题.高手请进
- Socket问题
- datagrid的具体某一行某一列的值。
- WPF 自定义窗体 拖动改变窗体大小时,如何用代码实现虚线框的效果?
- 请教uri路径问题及图片资源设置,
导入excel 12组件到项目 //实例化一个Excel应用程序对象 Microsoft.Office.Interop.Excel.application myexcel = new Microsoft.Office.Interop.Excel.Application(); //添加工作表
myexcel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet myworksheet = (Microsoft.Office.Interop.Excel.Worksheet) myexcel.Worksheets["Sheet1"]; //定义一个区域范围 Microsoft.Office.Interop.Excel.Range myrange = myexcel.get_Range(myexcel.Cells[1, 1], myexcel.Cells[3, 3]); //显示这个excel程序 myexcel.Visible = true ;
但此时的Excel表格是一个空的表格,没有任何内容,下面就来介绍如何往Excel表格中输入数据。 (3).往Excel表格中输入数据:
在命名空间"Excel"中,还定义了一个类"Cell",这个类所代表的就是Excel表格中的一个下单元。通过给差"Cell"赋值,从而实现往Excel表格中输入相应的数据,下列代码功能是打开Excel表格,并且往表格输入一些数据。
myexcel.Caption = " 花名册";
myworksheet .Cells[1, 1] = " 花名册";
myworksheet .Cells[2, 1] = "姓名"; (4). Visual C#调用Excel表格,
了解了上面的这些知识,得到完成上述功能的程序代码就显得比较容易了,函数具体如下: Excel.ApplicationClass Mylxls = new Excel.ApplicationClass();
Mylxls.Application.Workbooks.Add(true);
myexcel.Caption = " 花名册";
myworksheet .Cells[1, 1] = " 花名册";
myworksheet .Cells[2, 1] = "姓名";
myworksheet .Cells[2, 2] = "性别";
myworksheet .Cells[2, 3] = "出生年月"; //合并单元格(第一行的第一列至第3列)
myworksheet .get_Range(myworksheet .Cells[1, 1], myworksheet .Cells[1, 3]).MergeCells = true;
//逐行写入数据,dt为DataTable对象,从第三行开始写数据。 int i=3;
foreach(DataRow row in dt.Rows)
{
myworksheet .Cells[i, 1] = row["姓名"].ToString();
myworksheet .Cells[i, 2] = row["性别"].ToString();
myworksheet .Cells[i, 3] = row["出生年月"].ToString(); i++; }还可以,用oledb直接写入,那样就不需要导入这么麻烦了
excel也是跟sql一样可以用sql 语句操作的
{
string XX = Label1.Text + Label3.Text;
Export("application/ms-excel", XX + "月份采购计划.xls");
} public override void VerifyRenderingInServerForm(Control control)
{ } private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
using Aspose.Cells;Workbook workbook = new Workbook(); Worksheet sheet = (Worksheet)workbook.Worksheets[0]; sheet.Cells["A1"].PutValue("TEST1"); sheet.Cells["B1"].PutValue("日期");
String filename = string.Format("{0}{1}.xls", "saleranklist", Convert.ToDateTime(DateTime.Now).ToString("yyyyMMdd")); //文件默认命名方式,可以自定义 Response.ContentType = "application/ms-excel;charset=utf-8"; Response.AddHeader("content-disposition", "attachment; filename=" + filename); System.IO.MemoryStream memStream = workbook.SaveToStream(); Response.BinaryWrite(memStream.ToArray());
Response.End();
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = "SELECT * FROM [t_alibaba_data$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
ds = new DataSet();
myCommand.Fill(ds);
上面从t_alibaba_data.xlsx文件中读取数据到Dataset类型的ds中
/// 将指定的Dataset导出到Excel文件
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int Export(string path, System.Data.DataTable dt)
{
try
{
Microsoft.Office.Interop.Excel.Application excelkccx = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook wb = excelkccx.Workbooks.Add(true);
Range rngA = (Range)excelkccx.Columns["A", Type.Missing];//设置单元格格式
rngA.NumberFormatLocal = "@";//字符型格式
//Range rngD = (Range)excelkccx.Columns["D", Type.Missing];//设置单元格格式
//rngD.NumberFormatLocal = "0.00";
//Range rngE = (Range)excelkccx.Columns["E", Type.Missing];//设置单元格格式
//rngE.NumberFormatLocal = "0.00";
//Range rngF = (Range)excelkccx.Columns["F", Type.Missing];//设置单元格格式
//rngF.NumberFormatLocal = "0.00";
//Range rngAll = (Range)excelkccx.Columns["B","C"];
//rngAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //字段水平居中
//Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)excelkccx.Workbooks[1].Worksheets[1];
//string name = ws.Name; //获取sheet名称
//System.Data.DataTable dt = ds.Tables[0];
int row = 2;
for (int i = 0; i < dt.Columns.Count; i++)
{
excelkccx.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
excelkccx.Cells[row, j + 1] = dt.Rows[i][j].ToString();
}
row++;
}
wb.SaveAs(path);
wb.Close(false, null, null);
excelkccx.Quit();
wb = null;
excelkccx = null;
return 1;
}
catch(Exception ex)
{
return 0;
}
}
这个方法你直接拿去可以用 我一直在用的 不谢哈
{
try
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = ""; //DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列,加\n
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + ",";
} }
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息 int fees = 0; //逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列,加\n
{
ls_item += row[i].ToString().Trim() + "\n";// row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString().Trim() + ",";
}
} resp.Write(ls_item);
ls_item = "";
} resp.End();
}
catch (Exception ex)
{
// Response.Write("<script>alert('"+ex .Data +ex.Message +ex.Source +ex.TargetSite+"');history.go(-1);</script>");
throw;
}
} 这个是CSV的-,-