用C#实现EXCEL里面的规划求解

[xieyt]

如题~ 谁有做过的最好能给个例子· 谢谢

[jueyingfd]

兄弟，你是想要Web的还是WinForm的？两个都给你了，呵呵：

//Web的
    protected void btn_ToExcel_Click(object sender, EventArgs e)
    {
        Export(this.gv_Contract, "application/ms-excel", string.Format("合同列表_{0}.xls", Session["UserName"].ToString()));
    }
    private void Export(GridView gv, string filetype, string filename)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "UTF-8";
        Response.ContentEncoding = Encoding.GetEncoding("UTF-8");
        Response.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", HttpUtility.UrlEncode(filename)));
        Response.ContentType = filetype;
        EnableViewState = false;
        StringWriter tw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        ClearControls(gv);

        gv.RenderControl(hw);
        Response.Write(tw.ToString());
        Response.End();
    }

    public override void VerifyRenderingInServerForm(Control control)
    { }

    private void ClearControls(GridView gv)
    {
        for (int i = 0; i < gv.Rows.Count; i++)
        {
            gv.Rows[i].Cells[30].Controls.Clear();
        }
        gv.Columns[30].Visible = false;
    }

//WinForm的
        #region 将DataGridView控件中数据导出到Excel
        /// <summary>
        /// 将DataGridView控件中数据导出到Excel
        /// </summary>
        /// <param name="gridView">DataGridView对象</param>
        /// <param name="isShowExcle">是否显示Excel界面</param>
        /// <returns></returns>
        public bool ExportDataGridview(DataGridView gridView, bool isShowExcle)
        {
            if (gridView.Rows.Count == 0)
                return false;
            //建立Excel对象
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcle;
            //生成字段名称
            for (int i = 0; i < gridView.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
            }
            //填充数据
            for (int i = 0; i < gridView.RowCount - 1; i++)
            {
                for (int j = 0; j < gridView.ColumnCount; j++)
                {
                    if (gridView[j, i].ValueType == typeof(string))
                    {
                        excel.Cells[i + 2, j + 1] = "'" + gridView[j, i].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
                    }
                }
            }
            return true;
        }
        #endregion

[格拉]

直接用DATAGRIDVIEW，然后自己判断吧

[zealot1021]

导出Excel吗？

/// <summary>
        /// 导出excel方法
        /// </summary>
        /// <param name="title"></param>
        /// <param name="exprotGrid"></param>
        /// <returns></returns>
        public static string exprotExcel(string title, DataGridView exprotGrid)
        {
            string message = "";
            if (exprotGrid.Rows.Count == 0)
            {
                message = "当前数据为空，不执行导出";
                return message;
            }
            Excel.Application exlapp = new Excel.Application();
            if (exlapp == null)
            {
                message = "excel无法启动";
                return message;
            }
            Excel.Workbook exlbook = exlapp.Workbooks.Add(true);
            Excel.Worksheet exlsheet = (Excel.Worksheet)exlbook.Worksheets[1];

            Excel.Range range = exlsheet.get_Range(exlapp.Cells[1, 1], exlapp.Cells[1, exprotGrid.ColumnCount]);
            range.MergeCells = true;
            exlapp.ActiveCell.FormulaR1C1 = title;
            exlapp.ActiveCell.Font.Size = 20;
            exlapp.ActiveCell.Font.Bold = true;
            exlapp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;

            int colIndex = 0;
            int rowIndex = 0;
            int colCount = exprotGrid.ColumnCount;
            int rowCount = exprotGrid.RowCount;
            object[,] objdata = new object[rowCount + 1, colCount];

            for (colIndex = 0; colIndex < colCount; colIndex++)
            {
                objdata[rowIndex, colIndex] = exprotGrid.Columns[colIndex].HeaderText;
            }
            for (rowIndex = 0; rowIndex < rowCount; rowIndex++)
            {
                for (colIndex = 0; colIndex < colCount; colIndex++)
                {
                    if (exprotGrid[colIndex, rowIndex].Value != null)
                    {
                        if (exprotGrid[colIndex, rowIndex].Value.ToString().ToLower() == "true")
                        {
                            objdata[rowIndex + 1, colIndex] = "是";
                        }
                        else if (exprotGrid[colIndex, rowIndex].Value.ToString().ToLower() == "false")
                        {
                            objdata[rowIndex + 1, colIndex] = "否";
                        }
                        else
                        {
                            objdata[rowIndex + 1, colIndex] = exprotGrid[colIndex, rowIndex].Value.ToString();
                        }
                    }
                    else
                    {
                        objdata[rowIndex + 1, colIndex] = "";
                    }
                }
                Application.DoEvents();
            }

            exlapp.get_Range(exlapp.Cells[2, 1], exlapp.Cells[2, colIndex]).Font.Bold = true;
            range = exlsheet.get_Range(exlapp.Cells[2, 1], exlapp.Cells[rowCount + 2, colCount]);
            range.Value2 = objdata;
            try
            {
                exlapp.Cells.EntireColumn.AutoFit();
                exlapp.Cells.VerticalAlignment = Excel.Constants.xlCenter;
                exlapp.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
                exlapp.Visible = true;
            }
            catch
            {
                message = "保存出错，请检查";
                return message;
            }
            return message;
        }

[zhjchinese]

如果只是把数据导出来..你可以用流的方式生成HTML文件后,改下后缀就行了.这样比直接调 用EXECL要快的多,并且不用判断客户端是否安装了EXECL,同样可以设定样式这些.

[zhjchinese]

这是我写的个共公函数,你可以看下是不是你想要的
/// <summary>
/// 将DataGridView里面的数据导出成excel文件或html文件
/// </summary>
/// <param name="dtvGrid">DataGridView</param>
/// <param name="TitleName">表头</param>
public void DataGridViewExportToExcel(DataGridView dtvGrid, string TitleName)
{
if (dtvGrid.Rows.Count < 1)
{
MessageBox.Show("没有数据，不能导出！");
return;
}
string str_filename = "";//要保存的文件路径
SaveFileDialog saveFdl = new SaveFileDialog();
saveFdl.Title = "请选择导出文件的路径";
saveFdl.DefaultExt = "xls";
saveFdl.Filter = "Excel文件|*.xls|HTML文件|*.html";
saveFdl.FileName = TitleName.Trim();
saveFdl.ShowDialog();
str_filename = saveFdl.FileName;
if (str_filename.IndexOf(":") < 0)
{
return; //被点了取消
}
Application.DoEvents();
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append(@"<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01 Transitional//EN'>");
strBuilder.Append(@"<html>");
strBuilder.Append(@"<head>");
strBuilder.Append(@"<title>Document</title>");
strBuilder.Append(@"<meta HTTP-EQUIV='Content-Type' CONTENT='text/html; charset=utf-8'>");
strBuilder.Append(@"<style type='text/css'>");
strBuilder.Append(@"td{text-align:center; font-size:9pt; font-family:'宋体', sans-serif;}");
strBuilder.Append(@".strong {font-weight: bold; font-family:'宋体', sans-serif;}");
strBuilder.Append(@".strong_top {font-weight: bold; font-size:18pt; font-family:'宋体', sans-serif;}");
strBuilder.Append(@"</style>");
strBuilder.Append(@"</head>");
strBuilder.Append(@"<body>");
strBuilder.Append(@"<table>");
//表头
strBuilder.Append(@"<tr>");
strBuilder.Append(@"<td>");
strBuilder.Append(@"<table>");
strBuilder.Append(@"<tr>");
strBuilder.Append(@"<td colspan=" + dtvGrid.ColumnCount + " align='center' bgcolor='#CCCCCC'>");
strBuilder.Append(@" ");
strBuilder.Append(@"</td>");
strBuilder.Append(@"</tr>");
strBuilder.Append(@"<tr>");
strBuilder.Append(@"<td colspan=" + dtvGrid.ColumnCount + " align='center' class='strong_top' bgcolor='#CCCCCC'>");
strBuilder.Append(TitleName.Trim());
strBuilder.Append(@"</td>");
strBuilder.Append(@"</tr>");
strBuilder.Append(@"<tr>");
strBuilder.Append(@"<td colspan=" + dtvGrid.ColumnCount + " align='center' bgcolor='#CCCCCC'>");
strBuilder.Append(@" ");
strBuilder.Append(@"</td>");
strBuilder.Append(@"</tr>");
strBuilder.Append(@"</table>");
strBuilder.Append(@"</td>");
strBuilder.Append(@"</tr>");
//数据
strBuilder.Append(@"<tr>");
strBuilder.Append(@"<td>");
strBuilder.Append(@"<table border='1' cellpadding='1' cellspacing='0' bordercolor='#000000'>");
strBuilder.Append("</tr>");
//列头
strBuilder.Append("<tr class='strong'>");
for (int i = 0; i < dtvGrid.ColumnCount; i++)
{
strBuilder.Append("<td bgcolor='gray'>");
strBuilder.Append(dtvGrid.Columns[i].HeaderText.ToString());
strBuilder.Append("</td>");
}
strBuilder.Append("</tr>");
long irow_count = ((DataView)dtvGrid.DataSource).Count;//定义数据的总行数


for (int i = 0; i < irow_count; i++)
{
strBuilder.Append("<tr>");
for (int y = 0; y < dtvGrid.ColumnCount; y++)
{
if (i % 2 == 0)
{
strBuilder.Append("<td bgcolor='#99CCFF'>");
}
else
{
strBuilder.Append("<td bgcolor='#CCFFCC'>");
}
strBuilder.Append(" " + dtvGrid.Rows[i].Cells[y].Value.ToString());
strBuilder.Append("</td>");
}
strBuilder.Append("</tr>");
}
strBuilder.Append("</table>");
strBuilder.Append(@"</td>");
strBuilder.Append(@"</tr>");
strBuilder.Append(@"</table>");
strBuilder.Append("</body>");
strBuilder.Append("</html>");
using (StreamWriter write = new StreamWriter(str_filename))
{
write.Write(strBuilder.ToString());
write.Close();
}
MessageBox.Show("数据导出完成!");
}