求助：winform 根据条件将数据导出到同一工作薄的不同工作表（sheet）中

Question

本帖最后由 hwhtj 于 2013-10-08 16:51:22 编辑

Answer 1

C#通过引用office组件写excel文件

Answer 2

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = "Could not create excel object , possibly your computer cann't install excel";
                return false;
            }            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            worksheet.Name = "xxx";

Answer 3

给你一个操作excel的代码，你自己修改下。
    public class ExcelIO
    {
        private int _ReturnStatus;
        private string _ReturnMessage;        /// <summary>
        /// Execute return status 
        /// </summary>
        public int ReturnStatus
        {
            get { return _ReturnStatus; }
        }        /// <summary>
        /// Execute return info
        /// </summary>
        public string ReturnMessage
        {
            get { return _ReturnMessage; }
        }        public ExcelIO()
        {
        }        /// <summary>
        /// Import excel to dataset
        /// </summary>
        /// <param name="fileName">Excel full path file name</param>
        /// <returns>The dataset data</returns>
        public DataSet ImportExcel(string fileName)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = "Could not create excel object , possibly your computer cann't install excel";
                return null;
            }            Microsoft.Office.Interop.Excel.Workbook workbook;
            try
            {
                workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
            }
            catch
            {
                _ReturnStatus = -1;
                _ReturnMessage = "Excel file is opening now , please save and exit";
                return null;
            }            int n = workbook.Worksheets.Count;
            string[] SheetSet = new string[n];
            System.Collections.ArrayList al = new System.Collections.ArrayList();
            for (int i = 1; i <= n; i++)
            {
                SheetSet[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;
            }            workbook.Close(null, null, null);
            xlApp.Quit();
            if (workbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
            }
            if (xlApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
            }
            GC.Collect();            DataSet ds = new DataSet();
            string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                OleDbDataAdapter da;
                for (int i = 1; i <= n; i++)
                {
                    string sql = "select * from [" + SheetSet[i - 1] + "$] ";
                    da = new OleDbDataAdapter(sql, conn);
                    da.Fill(ds, SheetSet[i - 1]);
                    da.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }
            return ds;
        }        /// <summary>
        /// Export datatable to excel
        /// </summary>
        /// <param name="reportName">The report name</param>
        /// <param name="dt">The source datatable</param>
        /// <param name="saveFileName">Excel full path file name</param>
        /// <returns>True if export success , otherwise false</returns>
        public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
        {
            if (dt == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = "DataSet is empty";
                return false;
            }            bool fileSaved = false;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                _ReturnStatus = -1;
                _ReturnMessage = "Could not create excel object , possibly your computer cann't install excel";
                return false;
            }            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            worksheet.Cells.Font.Size = 10;
            Microsoft.Office.Interop.Excel.Range range;            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;            worksheet.Cells[1, 1] = reportName;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
                //range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(79, 129, 189));
                range.Font.Bold = true;
            }            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 3, i + 1];
                    if (r % 2 == 0)
                    {
                        //range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(211, 223, 238));
                    }
                    else
                    {
                        //range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 255, 255));
                    }
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }            range = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
            range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexNone, System.Drawing.Color.FromArgb(123, 160, 205));            if (dt.Rows.Count > 0)
            {
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(123, 160, 205));
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            }            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    _ReturnStatus = -1;
                    _ReturnMessage = "Export file error , possibly this file is opening now \n" + ex.Message;
                }
            }
            else
            {
                fileSaved = false;
            }            if (range != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                range = null;
            }
            if (worksheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                worksheet = null;
            }
            if (workbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
            }
            if (workbooks != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                workbooks = null;
            }
            xlApp.Application.Workbooks.Close();
            xlApp.Quit();
            if (xlApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
            }
            GC.Collect();
            return fileSaved;
        }
    }

Answer 4

谢谢QuickPai，我不是不太明白。想要个示例

Answer 5

http://blog.csdn.net/happy09li/article/details/7431967