JBLEAR-STD/Stone.WinForm/Stone.Common/MyExport.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data;

namespace Stone.Common
{
    public class MyExport
    {
        public static void ShowExport(DataGridView dgrd)
        {
            frmExport dialog = new frmExport();
            dialog.ShowExport(dgrd);
            dialog.Dispose();
        }

        public static void List(CheckedListBox clst, DataGridView dgrd)
        {
            try
            {
                clst.Items.Clear();
                clst.DisplayMember = "HeaderText";
                clst.ValueMember = "Name";

                foreach (DataGridViewColumn col in dgrd.Columns)
                {
                    if (col.Visible)
                        clst.Items.Add(col, true);
                }
            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }
        }

        #region old
        /*
        public static void ExportExcel(string filename, CheckedListBox clst, DataGridView dgrd)
        {
            StreamWriter sw = new StreamWriter(filename, false, System.Text.Encoding.GetEncoding("GB2312"));
            string str = " ";
            try
            {
                //写标题
                for (int i = 0; i < clst.CheckedItems.Count; i++)
                {
                    if (i > 0)
                        str += "\t ";
                    str += ((DataGridViewColumn)clst.CheckedItems[i]).HeaderText;
                }
                sw.WriteLine(str);

                //写记录
                foreach (DataGridViewRow row in dgrd.Rows)
                {
                    string tempStr = " ";
                    for (int k = 0; k < clst.CheckedItems.Count; k++)
                    {
                        if (k > 0)
                            tempStr += "\t ";
                        //tempStr += "\"" + row.Cells[((DataGridViewColumn)clst.CheckedItems[k]).Name].Value + "\"";
                        tempStr += row.Cells[((DataGridViewColumn)clst.CheckedItems[k]).Name].Value;
                    }
                    sw.WriteLine(tempStr);
                }

                MyMessageBox.ShowInfoMessage(filename + " 导出成功！");
            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }
            finally
            {
                sw.Close();
            }
        }
        */
        #endregion

        //public static void ExportExcel(string filename, CheckedListBox clst, DataGridView dgrd)
        //{
        //    try
        //    {

        //        //将Excel模板文件复制到指定的位置
        //        if (File.Exists(filename)) File.Delete(filename);

        //        //创建字段
        //        string createSQL = "CREATE TABLE [Sheet1]( ";
        //        string insertSQL = "insert into [Sheet1$]( ";
        //        for (int i = 0; i < clst.CheckedItems.Count; i++)
        //        {
        //            createSQL += "[" + ((DataGridViewColumn)clst.CheckedItems[i]).HeaderText.Trim() + "] nvarchar,";
        //            insertSQL += "[" + ((DataGridViewColumn)clst.CheckedItems[i]).HeaderText.Trim() + "],";
        //        }
        //        createSQL = createSQL.Substring(0, createSQL.Length - 1); //去掉最后一个逗号
        //        createSQL += ")";
        //        insertSQL = insertSQL.Substring(0, insertSQL.Length - 1); //去掉最后一个逗号
        //        insertSQL += ") values(";


        //        MyExcelDatabase.OpenDatabase(filename, true);
        //        MyExcelDatabase.ExecuteNonQuery(createSQL);

        //        foreach (DataGridViewRow row in dgrd.Rows)
        //        {
        //            string tempStr = insertSQL;
        //            for (int k = 0; k < clst.CheckedItems.Count; k++)
        //            {
        //                tempStr += "'" + MyStrings.GetString(row.Cells[((DataGridViewColumn)clst.CheckedItems[k]).Name].Value.ToString()) + "',";
        //            }
        //            tempStr = tempStr.Substring(0, tempStr.Length - 1); //去掉最后一个逗号
        //            tempStr += ")";

        //            MyExcelDatabase.ExecuteNonQuery(tempStr);
        //        }
        //        MyExcelDatabase.CloseDatabase();

        //        MyMessageBox.ShowInfoMessage(filename + " 导出成功！");
        //    }
        //    catch (Exception ex)
        //    {
        //        MyMessageBox.ShowErrorMessage(ex.Message);
        //    }
        //    finally
        //    {
        //        MyExcelDatabase.CloseDatabase();
        //    }
         
        //}


        public static void ExportExcel(string filename, CheckedListBox clst, DataGridView dgrd)
        {
            try
            {
                MyExcel excel = new MyExcel();
                excel.NewExcel(filename);
                excel.NewSheet("Sheet1");
                int rowindex = 0;    //当前列所在位置

                //创建第一行的字段
                excel.CreateRow(rowindex);
                for (int i = 0; i < clst.CheckedItems.Count; i++)
                {
                    excel.CreateCell(rowindex, i);
                    excel.SetCellValue(rowindex, i, ((DataGridViewColumn)clst.CheckedItems[i]).HeaderText.Trim());
                }

                rowindex++;
                foreach (DataGridViewRow row in dgrd.Rows)
                {
                    excel.CreateRow(rowindex);
                    for (int k = 0; k < clst.CheckedItems.Count; k++)
                    {
                        excel.CreateCell(rowindex, k);
                        excel.SetCellValue(rowindex, k, row.Cells[((DataGridViewColumn)clst.CheckedItems[k]).Name].Value.ToString());
                    }
                    rowindex++;
                }
                excel.Save();

                MyMessageBox.ShowInfoMessage(filename + " 导出成功！");
            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }
            finally
            {
                MyExcelDatabase.CloseDatabase();
            }

        }

        public static void ExportExcel(string filename, string title, DataSet dsData)
        {
            try
            {
                MyExcel excel = new MyExcel();
                excel.NewExcel(filename);
                excel.NewSheet(title);
                

                //创建第一行的标题列
                int rowindex = 0;
                excel.CreateRow(rowindex);
                excel.CreateCell(rowindex, 0);
                excel.SetCellValue(rowindex, 0, title);

                //创建第二行的字段
                rowindex++;
                excel.CreateRow(rowindex);
                for (int i = 0; i < dsData.Tables[0].Columns.Count; i++)
                {
                    excel.CreateCell(rowindex, i);
                    excel.SetCellValue(rowindex, i, dsData.Tables[0].Columns[i].ColumnName);
                }

                rowindex++;
                foreach (DataRow drData in dsData.Tables[0].Rows)
                {
                    excel.CreateRow(rowindex);
                    for (int k = 0; k < dsData.Tables[0].Columns.Count; k++)
                    {
                        excel.CreateCell(rowindex, k);
                        excel.SetCellValue(rowindex, k, drData[dsData.Tables[0].Columns[k].ColumnName].ToString());

                        
                    }
                    rowindex++;
                }
                excel.Save();

                MyMessageBox.ShowInfoMessage(filename + " 导出成功！");
            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }


        }

        public static void ExportExcelX(string filename, CheckedListBox clst, DataGridView dgrd)
        {
            try
            {
                MyExcelX excel = new MyExcelX();
                excel.NewExcel(filename);
                excel.NewSheet("Sheet1");
                int rowindex = 0;    //当前列所在位置

                //创建第一行的字段
                excel.CreateRow(rowindex);
                for (int i = 0; i < clst.CheckedItems.Count; i++)
                {
                    excel.CreateCell(rowindex, i);
                    excel.SetCellValue(rowindex, i, ((DataGridViewColumn)clst.CheckedItems[i]).HeaderText.Trim());
                }

                rowindex++;
                foreach (DataGridViewRow row in dgrd.Rows)
                {
                    excel.CreateRow(rowindex);
                    for (int k = 0; k < clst.CheckedItems.Count; k++)
                    {
                        excel.CreateCell(rowindex, k);
                        //excel.SetCellValue(rowindex, k, row.Cells[((DataGridViewColumn)clst.CheckedItems[k]).Name].Value.ToString());

                        if (((DataGridViewColumn)clst.CheckedItems[k]).ValueType == typeof(System.Int32))
                        {
                            if (row.Cells[((DataGridViewColumn)clst.CheckedItems[k]).Name].Value != DBNull.Value)
                            {
                                excel.SetCellValue(rowindex, k, Convert.ToDouble(row.Cells[((DataGridViewColumn)clst.CheckedItems[k]).Name].Value));
                            }
                        }
                        else
                        {
                            excel.SetCellValue(rowindex, k, row.Cells[((DataGridViewColumn)clst.CheckedItems[k]).Name].Value.ToString());
                        }

                    }
                    rowindex++;
                }
                excel.Save();

                MyMessageBox.ShowInfoMessage(filename + " 导出成功！");
            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }


        }

        public static void ExportExcelX(string filename, string title, DataSet dsData)
        {
            try
            {
                MyExcelX excel = new MyExcelX();
                excel.NewExcel(filename);
                excel.NewSheet(title);


                //创建第一行的标题列
                int rowindex = 0;
                excel.CreateRow(rowindex);
                excel.CreateCell(rowindex, 0);
                excel.SetCellValue(rowindex, 0, title);

                //创建第二行的字段
                rowindex++;
                excel.CreateRow(rowindex);
                for (int i = 0; i < dsData.Tables[0].Columns.Count; i++)
                {
                    excel.CreateCell(rowindex, i);
                    excel.SetCellValue(rowindex, i, dsData.Tables[0].Columns[i].ColumnName);
                }

                rowindex++;
                foreach (DataRow drData in dsData.Tables[0].Rows)
                {
                    excel.CreateRow(rowindex);
                    for (int k = 0; k < dsData.Tables[0].Columns.Count; k++)
                    {
                        excel.CreateCell(rowindex, k);
                        //excel.SetCellValue(rowindex, k, drData[dsData.Tables[0].Columns[k].ColumnName].ToString());

                        if (dsData.Tables[0].Columns[k].DataType == typeof(System.Int32))
                        {
                            if (drData[dsData.Tables[0].Columns[k].ColumnName] != DBNull.Value)
                            {
                                excel.SetCellValue(rowindex, k, Convert.ToDouble(drData[dsData.Tables[0].Columns[k].ColumnName]));
                            }
                        }
                        else
                        {
                            excel.SetCellValue(rowindex, k, drData[dsData.Tables[0].Columns[k].ColumnName].ToString());
                        }


                    }
                    rowindex++;
                }
                excel.Save();

                MyMessageBox.ShowInfoMessage(filename + " 导出成功！");
            }
            catch (Exception ex)
            {
                MyMessageBox.ShowErrorMessage(ex.Message);
            }


        }
    }
}