DangMY进行式

/// <summary>
/// Purpose：继承PrintDocument，实现DataGrid的打印
/// Author:  Dangmy
/// Date:    2007-03-09
/// Version: 1.0
/// </summary>
public class DataGridPrintDocument:System.Drawing.Printing.PrintDocument
{
    private Font fontCaption = new Font("宋体",12,FontStyle.Bold);   //表名字体
    private Font fontText = new Font("宋体",9);                             //表内容字体
    private int CellSpace = 2;                                                     //单元格与内容空隙
    private SolidBrush brushText = new SolidBrush(Color.Black);       //表格字体笔刷,指定为黑色
    private SolidBrush brushLine = new SolidBrush(Color.Gray);         //表格线条笔刷,指定为灰色
    private SolidBrush brushHead = new SolidBrush(Color.LightGray); //表头背景笔刷,指定为浅灰色
    private SolidBrush brushCell = new SolidBrush(Color.White);        //表格背景笔刷,指定为白色
    private DataGrid dataGrid;                     //打印DataGrid
    private DataTable dataTable;                //打印DataGrid数据表
    private int PageIndex;                          //当前打印的页号
    private int LineCount;                          //每页总共打印行数
    private int ColCount;                            //总共打印的列数
    private int ColIndex;                            //当前打印的列下标
    private int ColLastIndex;                       //上一页打印的列下标
    private int HeaderIndex;                       //当前打印的表头下标
    private int RecordCount;                       //总共打印的记录数
    private int RecordIndex;                        //当前打印记录下标
    private int RecordLastIndex;                  //上一页打印的记录下标
    private float x;                                    //打印X坐标
    private float y;                                    //打印Y坐标
    private bool IsPrintColHeader = false;       //是否每页打印表头
    private bool IsNewPageByCol = false;        //是否换页打印剩余列
    private bool IsNewPageByRow = false;      //是否换页打印剩余行
    private bool IsPreviewed = false;             //是否已生成预览,如果已生成预览,再预览窗口点击打印时,弹出打印机对话框

    private void InitializeComponent()
    {
       
    }

    //构造函数
    public DataGridPrintDocument(DataGrid printDataGrid)
    {
        dataGrid = printDataGrid;
        dataTable = (DataTable)printDataGrid.DataSource;
    }

    //初始化全局变量
    private void InitVar()
    {          
        if(dataTable == null)
            return;
        RecordCount = dataTable.Rows.Count;
        ColCount = dataTable.Columns.Count;
        PageIndex = 1;     
        ColIndex = 0;      
        ColLastIndex = 0;       
        HeaderIndex = 0;       
        RecordIndex = 0;      
        RecordLastIndex = 0;   

}

    protected override void OnBeginPrint(PrintEventArgs e)
    {
        base.OnBeginPrint (e);
        if(IsPreviewed)
        {
            PrintDialog dlgPrint = new PrintDialog();
            dlgPrint.Document = this;
            if(dlgPrint.ShowDialog() != DialogResult.OK)
                e.Cancel = true;
        }   

        InitVar();        
    }

    //PrintDocumen事件重写
    protected override void OnPrintPage(PrintPageEventArgs e)
    {          
        base.OnPrintPage (e);
       
        if(dataTable == null)
            return;

        //计算每页打印行数
        if(DefaultPageSettings.Landscape)
        {
            LineCount = (DefaultPageSettings.PaperSize.Width -
                         DefaultPageSettings.Margins.Left -
                         DefaultPageSettings.Margins.Right -
                         (int)fontCaption.GetHeight(e.Graphics))/
                        ((int)fontText.GetHeight(e.Graphics) + CellSpace + CellSpace);
        }
        else
        {
            LineCount = (DefaultPageSettings.PaperSize.Height -
                         DefaultPageSettings.Margins.Top -
                         DefaultPageSettings.Margins.Bottom -
                         (int)fontCaption.GetHeight(e.Graphics))/
                        ((int)fontText.GetHeight(e.Graphics) + CellSpace + CellSpace);
        }          

        //如果是第一行，则打印表名
        if(PageIndex == 1 && ColIndex == 0)
            DrawCaption(e.Graphics);

        //重置行下标、列下标
        if(IsNewPageByCol)
        {
            RecordIndex = RecordLastIndex;
            ColIndex = ColLastIndex;               
        }          
        if(IsNewPageByRow)
        {
            RecordLastIndex = RecordIndex;
            ColLastIndex = 0;              
        }  
        y = this.DefaultPageSettings.Margins.Top;
       
        //如果是第一页或者每页都打印表头，则打印表头
        if(RecordIndex == 0 || IsPrintColHeader)
            DrawColHeader(e.Graphics);

        //循环打印数据行
        int cnt = 0;
        for( ; RecordIndex<RecordCount; RecordIndex++)
        {
            if( cnt++ == LineCount)
            {
                if(!IsNewPageByCol)
                    IsNewPageByRow = true;
                break;
            }
            if( RecordIndex == RecordCount-1)
            {
                IsNewPageByRow = false;
            }                  
            DrawRow(e.Graphics,RecordIndex);
        }
        ColLastIndex = ColIndex;
       
        //换页
        if(IsNewPageByRow || IsNewPageByCol)
        {
            PageIndex++;
            e.HasMorePages = true;
        }
        IsPreviewed = true;
    }
   
    //打印报表名称
    private void DrawCaption(Graphics g)
    {
        x = this.DefaultPageSettings.Margins.Left;
        y = this.DefaultPageSettings.Margins.Top + fontCaption.GetHeight(g);
        g.DrawString(dataGrid.CaptionText,fontCaption,brushText,x,y);
    }
   
    //打印表头
    private void DrawColHeader(Graphics g)
    {
        x = this.DefaultPageSettings.Margins.Left + CellSpace;
        y += fontCaption.GetHeight(g) + fontText.GetHeight(g) + CellSpace*3;
        Pen penLine = new Pen(brushLine);
        if(!IsNewPageByCol)
            HeaderIndex = 0;
        for( ; HeaderIndex<ColCount; HeaderIndex++ )
        {  
            //超出页宽，停止打印
            if(DefaultPageSettings.Landscape)//横向打印
            {
                if(x > DefaultPageSettings.PaperSize.Height - DefaultPageSettings.Margins.Top - DefaultPageSettings.Margins.Bottom)
                    break;
            }
            else//纵向打印
            {
                if(x > DefaultPageSettings.PaperSize.Width - DefaultPageSettings.Margins.Left - DefaultPageSettings.Margins.Right)
                    break;
            }
            g.FillRectangle(brushHead,x,y,dataGrid.TableStyles[0].GridColumnStyles[HeaderIndex].Width + CellSpace*2,fontText.GetHeight(g) + CellSpace*2);
            g.DrawRectangle(penLine,x,y,dataGrid.TableStyles[0].GridColumnStyles[HeaderIndex].Width + CellSpace*2,fontText.GetHeight(g) + CellSpace*2);
            g.DrawString(dataTable.Columns[HeaderIndex].ToString(), fontText, brushText, x+CellSpace,y+CellSpace);             
            x += dataGrid.TableStyles[0].GridColumnStyles[HeaderIndex].Width + CellSpace;
           
        }
    }

    //打印一行
    private void DrawRow(Graphics g,int index)
    {
        x = this.DefaultPageSettings.Margins.Left + CellSpace;
        y += fontText.GetHeight(g) + CellSpace;
        Pen penLine = new Pen(brushLine);
       
        for(ColIndex = ColLastIndex; ColIndex<ColCount; ColIndex++ )
        {  
            //超出页宽，停止打印
            if(DefaultPageSettings.Landscape)//横向打印
            {
                if(x > DefaultPageSettings.PaperSize.Height - DefaultPageSettings.Margins.Top  - DefaultPageSettings.Margins.Bottom)
                {
                    IsNewPageByCol = true;

                    IsNewPageByRow = false;
                    break;
                }
            }
            else//纵向打印
            {
                if( x > DefaultPageSettings.PaperSize.Width - DefaultPageSettings.Margins.Left - DefaultPageSettings.Margins.Right)
                {
                    IsNewPageByCol = true;

                    IsNewPageByRow = false;
                    break;
                }
            }      
            g.FillRectangle(brushCell,x,y,dataGrid.TableStyles[0].GridColumnStyles[ColIndex].Width + CellSpace*2,fontText.GetHeight(g) + CellSpace*2);
            g.DrawRectangle(penLine,x,y,dataGrid.TableStyles[0].GridColumnStyles[ColIndex].Width + CellSpace*2,fontText.GetHeight(g) + CellSpace*2);
            g.DrawString(dataTable.Rows[index][ColIndex].ToString(), fontText, brushText, x + CellSpace,y + CellSpace);
            x += dataGrid.TableStyles[0].GridColumnStyles[ColIndex].Width + CellSpace; 
        }
        if(ColIndex == ColCount)
            IsNewPageByCol = false;
    }

}

 //打印预览调用
  private void btnPrint_Click(object sender, System.EventArgs e)
  {   
      DataGridPrintDocument printDocument = new DataGridPrintDocument(dataGrid1);  
      //页面设置
      PageSetupDialog dlgPage = new PageSetupDialog();
      dlgPage.Document = printDocument;
      dlgPage.ShowDialog();
      //预览
      PrintPreviewDialog dlgPreview = new PrintPreviewDialog();
      dlgPreview.Document = printDocument;
      dlgPreview.ShowDialog(); 
  }

   //自动调整DataGrid列宽
    public static void AutoSizeGrid(DataGrid dataGrid)
    {
        DataGridTableStyle tableStyle = new DataGridTableStyle();          
        System.Windows.Forms.DataGridTextBoxColumn colStyle;
        DataTable dt = (DataTable)dataGrid.DataSource;     
       
        //循环列
        for(int col=0; col<dt.Columns.Count; col++)
        {
            float width = 0;
            Graphics g = Graphics.FromHwnd(dataGrid.Handle);
            StringFormat sf = new StringFormat(StringFormat.GenericTypographic);
            SizeF size = new SizeF();
            //循环行，得到最大宽度
            for(int row=0; row<dt.Rows.Count; row++)
            {
                size = g.MeasureString(dataGrid[row,col].ToString(),dataGrid.Font,500,sf);
                if(size.Width > width)
                    width = size.Width;
            }
            g.Dispose();
           
            if(width < dataGrid.PreferredColumnWidth)
                width = dataGrid.PreferredColumnWidth;

            if(dataGrid.TableStyles.Count > 0)
                dataGrid.TableStyles[0].GridColumnStyles[col].Width = (int)width + 10;
            else
            {                  
                colStyle = new DataGridTextBoxColumn();
                colStyle.MappingName = dt.Columns[col].Caption;
                colStyle.HeaderText = dt.Columns[col].Caption;
                colStyle.Width = (int)width + 10;
                tableStyle.GridColumnStyles.Add(colStyle);
            }  
        }
        if(dataGrid.TableStyles.Count == 0)
        {
            tableStyle.RowHeadersVisible = false;
            dataGrid.TableStyles.Add(tableStyle);
        }
    }

///////////////////////////////////////////////////////////////////////////
//Purpose：Excel文件导入导出,需引用Microsoft Excel 11.0 Object Library
//Author: Dangmy
//Date: 2007-03-09
//Version: 1.0
///////////////////////////////////////////////////////////////////////////

public class ExcelIO
{
    private int _ReturnStatus;
    private string _ReturnMessage;

    /// <summary>
    /// 执行返回状态
    /// </summary>
    public int ReturnStatus
    {
        get{return _ReturnStatus;}
    }

    /// <summary>
    /// 执行返回信息
    /// </summary>
    public string ReturnMessage
    {
        get{return _ReturnMessage;}
    }

    public ExcelIO()
    {
    }

    /// <summary>
    /// 导入EXCEL到DataSet
    /// </summary>
    /// <param name="fileName">Excel全路径文件名</param>
    /// <returns>导入成功的DataSet</returns>
    public DataSet ImportExcel(string fileName)
    {
        //判断是否安装EXCEL
        Excel.Application xlApp=new Excel.ApplicationClass();          
        if(xlApp==null)
        {
            _ReturnStatus = -1;
            _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
            return null;
        }      

        //判断文件是否被其他进程使用           
        Excel.Workbook workbook;               
        try
        {
            workbook = xlApp.Workbooks.Open(fileName,0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
        }
        catch
        {
            _ReturnStatus = -1;
            _ReturnMessage = "Excel文件处于打开状态，请保存关闭";
            return null;
        }      
       
        //获得所有Sheet名称
        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] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
        }
       
        //释放Excel相关对象
        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();
       
        //把EXCEL导入到DataSet
        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>
    /// 把DataTable导出到EXCEL
    /// </summary>
    /// <param name="reportName">报表名称</param>
    /// <param name="dt">数据源表</param>
    /// <param name="saveFileName">Excel全路径文件名</param>
    /// <returns>导出是否成功</returns>
    public bool ExportExcel(string reportName,DataTable dt,string saveFileName)
    {
        if(dt==null)
        {
            _ReturnStatus = -1;
            _ReturnMessage = "数据集为空！";
            return false;          
        }

        bool fileSaved=false;
        Excel.Application xlApp=new Excel.ApplicationClass();  
        if(xlApp==null)
        {
            _ReturnStatus = -1;
            _ReturnMessage = "无法创建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
        worksheet.Cells.Font.Size = 10;
        Excel.Range range;

        long totalCount=dt.Rows.Count;
        long rowRead=0;
        float percent=0;

        worksheet.Cells[1,1]=reportName;
        ((Excel.Range)worksheet.Cells[1,1]).Font.Size = 12;
        ((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=(Excel.Range)worksheet.Cells[2,i+1];
            range.Interior.ColorIndex = 15;
            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();
            }
            rowRead++;
            percent=((float)(100*rowRead))/totalCount;
        }
       
        range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]);
        range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
        if( dt.Rows.Count > 0)
        {
            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(dt.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;
                _ReturnStatus = -1;
                _ReturnMessage = "导出文件时出错,文件可能正被打开！\n"+ex.Message;
            }
        }
        else
        {
            fileSaved=false;
        }          
   
        //释放Excel对应的对象
        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;
    }
}