向“超级高手”请教多个表Excel导出问题。。。

有这么一个结构：如图[img=http://static14.photo.sina.com.cn/bmiddle/49842f47t6ab3c105675d&690][/img]
这几个表 有一个共同的字段shipid，我现在要将这些信息全部导入Excel中。

我用left join 和inner join都是强制将这几个表关联在一起，我不想这样做。

要求：
     1. 几个表的内容相对独立（取出了5个datatable）
     2. 针对一个客户，将这几个表一下子导入一个Excel

盼望：真正的高手能告诉我思路，最好能有一段详细代码。感激不尽！

10 个解决方案

#1

这个问题，个人觉得很难，不知道有没有解？
自己顶。

#2

把数据表分别放到Excel工作表中

#3

如何放入？这个不会！

#4

打开excel模板。遍历dataset，通过表循环添加数据到单元格。通过dataset导入excel

#5

   public static bool ExportExcel(string strFileName, DataSet ds)
        {
            if (ds == null || ds.Tables.Count == 0)
                return false;


            Excel.Application appExcel = new Excel.Application();
            Excel.Workbook workbookData;
            Excel.Worksheet worksheetData;

            // set culture to US
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");


            workbookData = appExcel.Workbooks.Add(Missing.Value);

            int iSheet = 0;

            DataTable dt = new DataTable();

            for (int k = 0; k< ds.Tables.Count; k++)
            {
                worksheetData = (Excel.Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                dt = ds.Tables[k];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheetData.Cells[1, i + 1] = dt.Columns[i].ColumnName.Trim();
                }

                iSheet += 1;

                Excel.Range xlRang = null;
                int iRowCount = dt.Rows.Count;
                int iParstedRow = 0, iCurrSize = 0;
                int iEachSize = 1000;   // each time you 
                int iColumnAccount = dt.Columns.Count;

                object[,] objVal = new object[iEachSize, iColumnAccount];
                try
                {
                    iCurrSize = iEachSize;
                    while (iParstedRow < iRowCount)
                    {
                        if ((iRowCount - iParstedRow) < iEachSize)
                            iCurrSize = iRowCount - iParstedRow;
                        for (int i = 0; i < iCurrSize; i++)
                        {
                            for (int j = 0; j < iColumnAccount; j++)
                                objVal[i, j] = dt.Rows[iParstedRow + i][j].ToString();
                        }
                        // Get Save Range from Excel WorkSheet
                        // such as  A1 H10, means From A to H Columns, and 1 to 10 rows
                        xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + 2)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString()
                            + (((int)(iParstedRow + iCurrSize + 1)).ToString()));

                        xlRang.Value2 = objVal;
                        iParstedRow = iParstedRow + iCurrSize;
                    }


                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
                    xlRang = null;

                }
                catch
                {
                    return false;
                }
            }
            // return to previous culture
            System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;

            workbookData.Saved = false;
            workbookData.SaveAs(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            appExcel.Quit();

            return true;
        }
//没有考虑一个sheet的列限制

#6

应该是最大数据行限制 好像是6万多行

#7

不同的表放入不同的Worksheets中就可以了:这是一个示例.(其他的表只需改Worksheets的号,和该table的名字)
 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
           Microsoft.Office.Interop.Excel.Workbook wb = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
           Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
         
           
           try
           {
               int iMaxRow = ds.Tables["excel"].Rows.Count;
               int iMaxCol = ds.Tables["excel"].Columns.Count;
               xlApp.Cells[1, 1] = "产品序号";
               xlApp.Cells[1, 2] = "产品名称";
               xlApp.Cells[1, 3] = "产地";
               xlApp.Cells[1, 4] = "有效";
               xlApp.Cells[1, 5] = "价格";
               xlApp.Cells[1, 6] = "日期";
               for (int irow = 0; irow < iMaxRow; irow++)
               {
                   for (int iclo = 0; iclo < iMaxCol; iclo++)
                   {
                       xlApp.Cells[irow + 2, iclo + 1] = ds.Tables["excel"].Rows[irow][iclo].ToString();
                   }
               }
               xlApp.Save("product.xls");
           }

#8

最后save,可以多层循环使代码优化

#9

给个例子，从ListView中导出，DataSet也是一样，希望对楼主有所帮助。

        public void ExportExcel()
        {
            //   DataTable xslTable=(DataTable)this.dgrd_Show.DataSource;//取得dataGrid绑定的DataSet
            //   if(xslTable==null) return;
            if (this.listViewQuery_IM.Items.Count == 0) return;

            string saveFileName = "";
            bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = "Sheet1";
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消

            Excel.Application xlApp = new Excel.Application();

            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel.","提示",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
                return;
            }

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            Excel.Range range;

            //   string oldCaption=this.listViewQuery_IM.ca.CaptionText;
            long totalCount = this.listViewQuery_IM.Items.Count;
            long rowRead = 0;
            float percent = 0;

            //写入表头
            for (int i = 0; i < this.listViewQuery_IM.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = this.listViewQuery_IM.Columns[i].Text.ToString();
            }

            //设置表头显示样式
            Excel.Range productTitle = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, this.listViewQuery_IM.Columns.Count]);
            productTitle.Font.ColorIndex = 5;
            productTitle.Font.Bold = false;
            productTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            productTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;


            //写入数值
            this.lblpro.Visible = true;
            for (int j = 0; j < this.listViewQuery_IM.Items.Count; j++)
            {
                worksheet.Cells[j + 2, 1] = this.listViewQuery_IM.Items[j].SubItems[0].Text;
                worksheet.Cells[j + 2, 2] = this.listViewQuery_IM.Items[j].SubItems[1].Text;
                worksheet.Cells[j + 2, 3] = this.listViewQuery_IM.Items[j].SubItems[2].Text;
                worksheet.Cells[j + 2, 4] = this.listViewQuery_IM.Items[j].SubItems[3].Text;
                worksheet.Cells[j + 2, 5] = this.listViewQuery_IM.Items[j].SubItems[4].Text;
                worksheet.Cells[j + 2, 6] = this.listViewQuery_IM.Items[j].SubItems[5].Text;
                worksheet.Cells[j + 2, 7] = this.listViewQuery_IM.Items[j].SubItems[6].Text;
                worksheet.Cells[j + 2, 8] = this.listViewQuery_IM.Items[j].SubItems[7].Text;
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;

                //显示填写进度
                this.lblpro.Text = "正在导出数据[" + percent.ToString("0.00") + "%]...";
                Application.DoEvents();
            }

            this.lblpro.Visible = false;


            range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[this.listViewQuery_IM.Items.Count + 2, this.listViewQuery_IM.Columns.Count]);
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;

            if (this.listViewQuery_IM.Columns.Count > 1)
            {
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            }

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    MessageBox.Show("导出Excel文件时出错,文件可能正被打开！\n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }

            xlApp.Quit();
            GC.Collect();//强行销毁
            //导出成功，是否打开文件提示
            if (fileSaved && File.Exists(saveFileName))
            {
                if (MessageBox.Show("文件导出成功，要打开文件吗？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
                {
                    System.Diagnostics.Process.Start(saveFileName);
                }
                else
                {
                    return;
                }
            } 

        }

#10

这是我写的，虽然有点麻烦，但是实现了需要的功能，大家一起研究。
【由于字数限制，只能贴出部分代码，希望能触类旁通】

                            #region 第一个子表的子表一：

                            sbd.Append("<table x:str border=0 cellpadding=0 cellspacing=0 width=518 style='border-collapse:");
                            sbd.Append("collapse;table-layout:fixed;width:389pt'>");

                            #region 第一个DataTable的数据

                                sbd.Append("<tr height=22 style='height:16.3pt'>");
                                sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>办事处编码</td>");
                                sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>办事处名称</td>");
                                sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>联系电话</td>");
                                sbd.Append("<td height=19 class=xl25 width=100 style='height:14.25pt;width:57pt;color:Red;'>所在省</td>");
                                sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>所在市</td>");
                                sbd.Append("<td height=19 class=xl25 width=76 style='height:14.25pt;width:57pt;color:Red;'>所在区</td>");

                                sbd.Append("</tr>");

                                for (int i = 0; i < dt.Rows.Count; i++)
                                {
                                    sbd.Append("<tr height=22 style='height:16.3pt'>");
                                    sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["OffId"].ToString().Trim() + "</td>");
                                    sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["OffName"].ToString().Trim() + "</td>");
                                    sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["Phone"].ToString().Trim() + "</td>");
                                    sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["Province"].ToString().Trim() + "</td>");
                                    sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["City"].ToString().Trim() + "</td>");
                                    sbd.Append("<td class=xl25 style='border-top:none;border-left:none'>" + dt.Rows[i]["County"].ToString().Trim() + "</td>");
                                    sbd.Append("</tr>");
                                }

                         

                            #endregion

                            sbd.Append("</table>");