winform 导出Excel问题

Question

怎么控制导出时，不导出第1列单元格和最后一列单元格的数据请各位指点一二。

Answer 1

你是从datagridview导出到excel？不导出datagridview的第一列和最后一列?
那只要判断下不就可以了嘛？？

Answer 2

using System;
using System.Text;
using System.Data;
using System.Threading;
using System.Windows.Forms;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;namespace HmDataPrinter
{
    /// <summary>
    /// Excel操作类
    /// </summary>
    public class HmExcelAssist
    {
        public static string saveFileName = string.Empty;           // 用于保存EXCEL文件的文档名
        public static DataTable dtDataSource = new DataTable();     // Excel数据源
        /// <summary>
        /// 实现DataGridView向DataTable的转换
        /// </summary>
        /// <param name="dvSource">DataGridView</param>
        public static void GridViewToTable(DataGridView dvSource)
        {
            dtDataSource = new DataTable();            DataColumn col;                     // 设置列
            for (int i = 1; i < dvSource.Columns.Count-1; i++)
            {
                col = new DataColumn();
                col.ColumnName = dvSource.Columns[i].HeaderText;
                dtDataSource.Columns.Add(col);
            }            DataRow dr;                         // 设置行
            for (int i = 0; i < dvSource.Rows.Count; i++)
            {
                dr = dtDataSource.NewRow();
                for (int j = 1; j < dvSource.Columns.Count-1; j++)
                {
                    dr[j] = 1 == j ? "'" + dvSource.Rows[i].Cells[j].Value.ToString() :
                        dvSource.Rows[i].Cells[j].Value.ToString();
                }
                dtDataSource.Rows.Add(dr);
            }
        }        
        /// <summary>
        /// 保存记录至Excel
        /// </summary>
        public static void SaveRecordToExcel()
        {
            saveFileName = string.Format("{0}{1}记录", DateTime.Now.Date.ToString("yyyyMMdd"),
                saveFileName);                                                  // 设置默认的保存文件名            SaveFileDialog saveDialog = new SaveFileDialog();                   // 保存文件对话框
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = saveFileName;            if (saveDialog.ShowDialog().Equals(DialogResult.Cancel))            // 单击了'取消'按钮,则返回
                return;            saveFileName = saveDialog.FileName;                                 // 设置新的保存文件名
            Thread tdSaveRecord = new Thread(new ThreadStart(SaveRecord));      // 启用线程保存数据
            tdSaveRecord.Start();
        }        #region Excel操作中私有的方法
        /// <summary>
        /// 读取信息并保存记录
        /// </summary>
        private static void SaveRecord()
        {
            string strMsg = string.Empty;                                       // 提示信息
            MessageBoxIcon msgIcon = MessageBoxIcon.Information;                // 设置消息框的图标            if (dtDataSource.Rows.Count == 0)
            {
                MessageBox.Show("没有要保存的数据!", "操作提示", MessageBoxButtons.OK, msgIcon);
                return;
            }            if (ExportExcel())                                                  // 保存数据至Excel文件中
            {
                strMsg = "记录导出完毕!";
            }
            else
            {
                strMsg = "记录导出出错,请重试!";
                msgIcon = MessageBoxIcon.Error;
            }
            MessageBox.Show(strMsg, "操作提示", MessageBoxButtons.OK, msgIcon);
        }
        /// <summary>
        /// 将 DataTable 数据保存至 Excel 文件中
        /// </summary>
        private static bool ExportExcel()
        {
            if (saveFileName.IndexOf(":") < 0) return false; //被点了取消            Excel.Application xlApp = new Excel.Application();
            object missing = System.Reflection.Missing.Value;            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return false;
            }
            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 strCaption = saveFileName.Remove(0, saveFileName.LastIndexOf('\\') + 1);
            strCaption = strCaption.Remove(strCaption.Length - 4, 4);            long totalCount = dtDataSource.Rows.Count;
            long rowRead = 0;
            float percent = 0;            range = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1],
                worksheet.Cells[2, dtDataSource.Columns.Count]);                    //标题占用前两行
            range.Merge(missing);                                                   //合并
            range.Font.Bold = true;                                                 //粗体设置
            range.Font.Size = 16;                                                   //字体大小设置 
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;              //水平对齐设置 
            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;                //垂直对齐设置
            //range.FormulaR1C1 = 公式;             //公式设置
            //range.ColumnWidth = 宽度;             //列宽设置
            //range.RowHeight = 行高;               //行高  
            worksheet.Cells[1, 1] = strCaption;            //写入字段
            for (int i = 0; i < dtDataSource.Columns.Count; i++)
            {
                worksheet.Cells[4, i + 1] = dtDataSource.Columns[i].ColumnName;
                range = (Excel.Range)worksheet.Cells[4, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
            }            //写入数值
            for (int r = 0; r < dtDataSource.Rows.Count; r++)
            {
                for (int i = 0; i < dtDataSource.Columns.Count; i++)
                {
                    worksheet.Cells[r + 5, i + 1] = dtDataSource.Rows[r][i];
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
                Application.DoEvents();
            }
            worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);            range = worksheet.get_Range(worksheet.Cells[4, 1],
                worksheet.Cells[dtDataSource.Rows.Count + 4, dtDataSource.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 (dtDataSource.Columns.Count > 1)
            {
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            }
            workbook.Close(missing, missing, missing);
            xlApp.Quit();            return true;
        }
        #endregion
    }
}测试代码：
          　　HmExcelAssist.saveFileName = this.strFileName;  // 设置文件名
                    HmExcelAssist.GridViewToTable(this.dgvModel);   // 保存数据
                    HmExcelAssist.SaveRecordToExcel();

Answer 3

// winform 从DataTable导出EXCEL文件
public void ExportExcelOffice(DataTable table, string filepath)
{    Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    excel.Visible = false;
    object ms = Type.Missing;
    Excel.Workbook wk = excel.Workbooks.Add(ms);
    Excel.Worksheet ws = wk.Worksheets[1] as Excel.Worksheet;
    for (int i = 0; i < table.Columns.Count; i++)
    {
        // 哪一列不要在这处理,跳过去就行了
        ws.Cells[1, i + 1] = table.Columns[i].ColumnName;
    }
    for (int i = 0; i < table.Rows.Count; i++)
    {
        for (int j = 0; j < table.Columns.Count; j++)
        {
            ws.Cells[i + 2, j + 1] = table.Rows[i][j].ToString();
        }
    }    if (File.Exists(filepath) == false)
    {
        Directory.CreateDirectory(filepath);
    }
    wk.SaveAs(filepath, ms, ms, ms, ms, ms, Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms);
    excel.Quit();}

Answer 4

1楼正解 还有个死办法
Excel.Application myExcel = new Excel.Application();
        myExcel.Application.Workbooks.Add(true);
        myExcel.Cells[1, 1] = name[0];
        myExcel.Cells[1, 2] = name[1];
        myExcel.Cells[1, 3] = name[2];
        //myExcel.Cells[1, 2]就是第一行第2列，这样的2唯数组形式可以理解了吧 但是这样做效率不很高