/// <summary>
/// 导出数据到Excel
/// </summary>
public void loadDataToExcel()
{
if (this.Rows.Count <= 0)
{
MessageBox.Show("没有数据，无法导出！");
return;
}
//创建Excel中的新表
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
oExcel.Application.Workbooks.Add(true);
int n = 0;
int i = 0;
//填充表头
for (i = 0; i < this.Columns.Count; i++)
{
oExcel.Cells[1, i + 1] = this.Columns[i].HeaderText;
}
for (n = 0; n < this.Rows.Count; n++)
for (i = 0; i < this.Columns.Count; i++)
{
try
{
oExcel.Cells[n + 2, i + 1] = this.Rows[n].Cells[i].Value.ToString();
}
catch (Exception)
{
oExcel.Cells[n + 2, i + 1] = "";
}
}
oExcel.Visible = true;
oExcel.Quit();
oExcel = null;
GC.Collect();
}
/// <summary>
/// 将datagridview的内容转换成DataTable
/// <param name="startCol">起始列,0表示第一列，依次类推</param>
/// <param name="endCol">终止列,1表示第一列，依次类推</param>
/// </summary>
/// <returns>如果转换成功返回DataTable,否则返回nothing</returns>
/// <remarks></remarks>
public DataTable convertToDataTable(int startCol,int endCol)
{
try
{
if (startCol > endCol)
{
MessageBox.Show("起始列不能大于终止列！");
return null;
}
if (startCol >= this.Columns.Count || endCol >= this.Columns.Count)
{
MessageBox.Show("起始列和终止列都不能大于总列数！");
return null;
}
//创建数据表格对象
DataTable dt=new DataTable();
int i,j;
//获取列名
for (i=startCol;i<endCol;i++)
{
dt.Columns.Add(Columns[i].ToString());
dt.Columns[i].Caption=Columns[i].HeaderText;
}
//获取行值
DataRow r;
object[] rowArray = new object[endCol];
for(i=0;i<Rows.Count;i++)
{
r=dt.NewRow(); //创建新行
for (j = 0; j < endCol; j++)
rowArray[j]=this.Rows[i].Cells[j].Value; //获取j行中各列值
r.ItemArray = rowArray;//设置新行r中的值
dt.Rows.Add(r);//添加新行r到dt数据表格中
}
if (dt.Rows.Count >0)
return dt; //存在数据返回数据表格
else
return null;
}catch(Exception )
{
return null;
}
}
/// <summary>
/// 将datagridview的内容转换成DataTable
/// </summary>
/// <returns>如果转换成功返回DataTable,否则返回nothing</returns>
/// <remarks></remarks>
public DataTable convertToDataTable()
{
try
{
//创建数据表格对象
DataTable dt = new DataTable();
int i, j;
//获取列名
for (i = 0; i < Columns.Count; i++)
{
dt.Columns.Add(Columns[i].ToString());
dt.Columns[i].Caption = Columns[i].HeaderText;
}
//获取行值
DataRow r;
object[] rowArray = new object[Columns.Count];
for (i = 0; i < Rows.Count; i++)
{
r = dt.NewRow(); //创建新行
for (j = 0; j < Columns.Count; j++)
rowArray[j] = this.Rows[i].Cells[j].Value; //获取j行中各列值
r.ItemArray = rowArray;//设置新行r中的值
dt.Rows.Add(r);//添加新行r到dt数据表格中
}
if (dt.Rows.Count > 0)
return dt; //存在数据返回数据表格
else
return null;
}
catch (Exception)
{
return null;
}
}
public void printPreview()
{
try
{
dataTable1 = new DataTable();
dataTable1 = convertToDataTable();
if (dataTable1 == null)
{
MessageBox.Show("打印失败！");
return;
}
ColsCount = dataTable1.Columns.Count;
printDocument1 = new PrintDocument();
this.printDocument1.PrintPage += new System.Drawing.Printing.PrintPageEventHandler(this.printDocument1_PrintPage);
PageSetupDialog pageSetup = new PageSetupDialog();
pageSetup.Document = printDocument1;
printDocument1.DefaultPageSettings = pageSetup.PageSettings;
if (pageSetup.ShowDialog() == DialogResult.Cancel)
{
return;
}
pLeft = printDocument1.DefaultPageSettings.Margins.Left;
pTop = printDocument1.DefaultPageSettings.Margins.Top;
pRight = printDocument1.DefaultPageSettings.Margins.Right;
pBottom = printDocument1.DefaultPageSettings.Margins.Bottom;
pWidth = printDocument1.DefaultPageSettings.Bounds.Width;
pHeight = printDocument1.DefaultPageSettings.Bounds.Height;
//将当前页分成基本的单元
x_unit = (pWidth - pLeft - pRight) / dataTable1.Columns.Count - 1;
pRecordNumber = (pHeight - pTop - pBottom - headHeight - subHeadHeight - footHeight - subFootHeight - y_unit) / y_unit;
if (dataTable1.Rows.Count > pRecordNumber)
if (dataTable1.Rows.Count % pRecordNumber == 0)
totalPage = dataTable1.Rows.Count / pRecordNumber;
else
totalPage = dataTable1.Rows.Count / pRecordNumber + 1;
else
totalPage = 1;
printDocument1.DocumentName = totalPage.ToString();
PrintPreviewDialog printPreview = new PrintPreviewDialog();
printPreview.Document = printDocument1;
printPreview.ShowDialog();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void printPreview(int startCol, int endCol)
{
try
{
dataTable1 =new DataTable();
dataTable1=convertToDataTable(startCol,endCol);
if (dataTable1 == null)
{
MessageBox.Show("打印失败！");
return;
}
ColsCount =dataTable1.Columns.Count;
printDocument1=new PrintDocument();
this.printDocument1.PrintPage += new System.Drawing.Printing.PrintPageEventHandler(this.printDocument1_PrintPage);
PageSetupDialog pageSetup=new PageSetupDialog();
pageSetup.Document =printDocument1;
printDocument1.DefaultPageSettings =pageSetup.PageSettings;
if (pageSetup.ShowDialog()==DialogResult.Cancel)
{
return ;
}
pLeft = printDocument1.DefaultPageSettings.Margins.Left;
pTop = printDocument1.DefaultPageSettings.Margins.Top;
pRight = printDocument1.DefaultPageSettings.Margins.Right;
pBottom = printDocument1.DefaultPageSettings.Margins.Bottom;
pWidth = printDocument1.DefaultPageSettings.Bounds.Width;
pHeight = printDocument1.DefaultPageSettings.Bounds.Height;
//将当前页分成基本的单元
x_unit=(pWidth -pLeft-pRight)/dataTable1.Columns.Count -1;
pRecordNumber=(pHeight -pTop -pBottom -headHeight -subHeadHeight -footHeight -subFootHeight -y_unit)/y_unit;
if (dataTable1.Rows.Count >pRecordNumber)
if (dataTable1.Rows.Count % pRecordNumber==0)
totalPage =dataTable1.Rows.Count /pRecordNumber;
else
totalPage =dataTable1.Rows.Count /pRecordNumber+1;
else
totalPage =1;
printDocument1.DocumentName =totalPage.ToString();
PrintPreviewDialog printPreview=new PrintPreviewDialog();
printPreview.Document =printDocument1;
printPreview.ShowDialog();
}catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void print(int startCol, int endCol)
{
try
{
dataTable1 =new DataTable();
dataTable1=convertToDataTable(startCol,endCol);
if (dataTable1 == null)
{
MessageBox.Show("打印失败！");
return;
}
ColsCount =dataTable1.Columns.Count;
printDocument1=new PrintDocument();
this.printDocument1.PrintPage += new System.Drawing.Printing.PrintPageEventHandler(this.printDocument1_PrintPage);
pLeft = printDocument1.DefaultPageSettings.Margins.Left;
pTop = printDocument1.DefaultPageSettings.Margins.Top;
pRight = printDocument1.DefaultPageSettings.Margins.Right;
pBottom = printDocument1.DefaultPageSettings.Margins.Bottom;
pWidth = printDocument1.DefaultPageSettings.Bounds.Width;
pHeight = printDocument1.DefaultPageSettings.Bounds.Height;
//将当前页分成基本的单元
x_unit=(pWidth -pLeft-pRight)/dataTable1.Columns.Count -1;
pRecordNumber=(pHeight -pTop -pBottom -headHeight -subHeadHeight -footHeight -subFootHeight -y_unit)/y_unit;
if (dataTable1.Rows.Count >pRecordNumber)
if (dataTable1.Rows.Count % pRecordNumber==0)
totalPage =dataTable1.Rows.Count /pRecordNumber;
else
totalPage =dataTable1.Rows.Count /pRecordNumber+1;
else
totalPage =1;
printDocument1.DocumentName =totalPage.ToString();
printDocument1.Print();
}catch(Exception)
{
MessageBox.Show("打印错误，请检查打印设置！");
}
}
public void print()
{
try
{
dataTable1 = new DataTable();
dataTable1 = convertToDataTable();
if (dataTable1 == null)
{
MessageBox.Show("打印失败！");
return;
}
ColsCount = dataTable1.Columns.Count;
printDocument1 = new PrintDocument();
this.printDocument1.PrintPage += new System.Drawing.Printing.PrintPageEventHandler(this.printDocument1_PrintPage);
pLeft = printDocument1.DefaultPageSettings.Margins.Left;
pTop = printDocument1.DefaultPageSettings.Margins.Top;
pRight = printDocument1.DefaultPageSettings.Margins.Right;
pBottom = printDocument1.DefaultPageSettings.Margins.Bottom;
pWidth = printDocument1.DefaultPageSettings.Bounds.Width;
pHeight = printDocument1.DefaultPageSettings.Bounds.Height;
//将当前页分成基本的单元
x_unit = (pWidth - pLeft - pRight) / dataTable1.Columns.Count - 1;
pRecordNumber = (pHeight - pTop - pBottom - headHeight - subHeadHeight - footHeight - subFootHeight - y_unit) / y_unit;
if (dataTable1.Rows.Count > pRecordNumber)
if (dataTable1.Rows.Count % pRecordNumber == 0)
totalPage = dataTable1.Rows.Count / pRecordNumber;
else
totalPage = dataTable1.Rows.Count / pRecordNumber + 1;
else
totalPage = 1;
printDocument1.DocumentName = totalPage.ToString();
printDocument1.Print();
}
catch (Exception)
{
MessageBox.Show("打印错误，请检查打印设置！");
}
}
private void printDocument1_PrintPage(Object sender,PrintPageEventArgs ev)
{
printRecordLeave=dataTable1.Rows.Count -printRecordComplete; //还有多少条记录没有打印
if (printRecordLeave>0)
if (printRecordLeave % pRecordNumber ==0)
pageNumber =printRecordLeave/pRecordNumber;
else
pageNumber =printRecordLeave/pRecordNumber+1;
else
pageNumber =0;
//正在打印的页数
printingPageNumber =0; //因为每打印一个新页都要计算还有多少页没有打印所以以打印的页数初始为0
//计算,余下的记录条数是否还可以在一页打印,不满一页时为假
if (dataTable1.Rows.Count-printingPageNumber*pRecordNumber>=pRecordNumber )
pageRecordNumber =pRecordNumber;
else
pageRecordNumber=(dataTable1.Rows.Count-printingPageNumber*pRecordNumber )% pRecordNumber ;
StringFormat fmt=new StringFormat();
fmt.LineAlignment = StringAlignment.Center;//上下对齐
fmt.FormatFlags = StringFormatFlags.LineLimit;//自动换行
Rectangle rect=new Rectangle(); //打印区域
Pen pen=new Pen(Brushes.Black,1); //打印表格线格式
while (printingPageNumber<=pageNumber)
{
fmt.Alignment = StringAlignment.Center;//表头中间对齐
rect.Width = pWidth - pLeft - pRight ;//表头和副表头宽度等于设置区域宽度
rect.Height = headHeight;
rect.X = pLeft;
rect.Y = pTop;
ev.Graphics.DrawString(headText, headFont, Brushes.Black, rect, fmt);//打印表头
fmt.Alignment = StringAlignment.Near;// '副表头左对齐
rect.Width = (pWidth - pLeft - pRight) / 2 - 1;
rect.Height = subHeadHeight;
rect.Y = pTop + headHeight;
ev.Graphics.DrawString(subHeadLeftText, subHeadFont, Brushes.Black, rect, fmt);//打印副表头左
fmt.FormatFlags = StringFormatFlags.DirectionRightToLeft;//右副表头文字从右往左排列
fmt.Alignment = StringAlignment.Near ; //右副表头右对齐
rect.X = pLeft + (pWidth - pLeft - pRight) / 2;
ev.Graphics.DrawString(subHeadRightText, subHeadFont, Brushes.Black, rect, fmt);//打印副表头右
fmt.Alignment = StringAlignment.Center;
rect.X = pLeft;
rect.Y = pTop + headHeight + subHeadHeight + (pRecordNumber + 1) * (y_unit) + subFootHeight;
rect.Height = footHeight;
rect.Width = pWidth - pLeft - pRight;
ev.Graphics.DrawString(footText, footFont, Brushes.Black, rect, fmt);// 打印表脚
fmt.Alignment = StringAlignment.Far;// 副表左左对齐
rect.X = pLeft;
rect.Y = pTop + headHeight + subHeadHeight + (pRecordNumber + 1) * (y_unit);
rect.Height = subFootHeight;
rect.Width = (pWidth - pLeft - pRight) / 2 - 1;
ev.Graphics.DrawString(subFootLeftText, subFootFont, Brushes.Black, rect, fmt);//打印左表脚
fmt.Alignment = StringAlignment.Near; //副表头右对齐
rect.X = pLeft + (pWidth - pLeft - pRight) / 2;
if (dataTable1.Rows.Count == 0 )
subFootRightText = "第" + totalPage + "页，共" + totalPage + "页";
else
subFootRightText = "第" + (totalPage - pageNumber + 1).ToString() + "页，共" + totalPage+ "页";
ev.Graphics.DrawString(subFootRightText, subFootFont, Brushes.Black, rect, fmt);// '打印右表脚
//得到datatable的所有列名
fmt.Alignment = StringAlignment.Center;
string []ColumnText=new string[dataTable1.Columns.Count];
string []ColumnID=new string[dataTable1.Columns.Count];
//画一条线
ev.Graphics.DrawLine(Pens.Black, pLeft, pTop + headHeight + subHeadHeight, pWidth - pLeft, pTop + headHeight + subHeadHeight);
for (int Cols = 0;Cols<dataTable1.Columns.Count;Cols++)
{ ColumnText[Cols] = dataTable1.Columns[Cols].Caption;
ColumnID[Cols] = dataTable1.Columns[Cols].ColumnName;
rect.X = pLeft + x_unit * Cols;
rect.Y = pTop + headHeight + subHeadHeight;
rect.Width = x_unit;
rect.Height = y_unit;
//画表头
ev.Graphics.DrawString(ColumnText[Cols], new Font(tableFont, FontStyle.Bold), drawBrush, rect, fmt);
}
ev.Graphics.DrawLine(Pens.Black, pLeft, rect.Height + rect.Y - 5, pWidth - pLeft, rect.Height + rect.Y - 5);
//结束---------------------得到datatable的所有列名
int printingLine = 0 ; //当前页面已经打印的记录行数
while(printingLine<pageRecordNumber)
{
dataGridRow = dataTable1.Rows[printRecordComplete]; //确定要当前要打印的记录的行号
for (Cols = 0;Cols<dataTable1.Columns.Count ;Cols++)
{ rect.X = pLeft + x_unit * Cols;
rect.Y = pTop + headHeight + subHeadHeight + (printingLine + 1) * (y_unit);
rect.Width = x_unit;
rect.Height = y_unit;
RectangleF convertedRectangle = rect;
if (dataGridRow[ColumnID[Cols]].ToString()!="")
ev.Graphics.DrawString(dataGridRow[ColumnID[Cols]].ToString(), tableFont, drawBrush, convertedRectangle, fmt);
}
printingLine += 1;
printRecordComplete += 1;
if (printRecordComplete >= dataTable1.Rows.Count)
{ ev.Graphics.DrawLine(Pens.Black, pLeft, rect.Y + rect.Height + 10, pWidth - pLeft, rect.Y + rect.Height + 10);
if (sum > 0 )
{
ev.Graphics.DrawString("总计：人民币" + (new PublicClass.ChineseMoney(Convert.ToDecimal(sum))).getChineseMoney()+"("+sum+")", tableFont, drawBrush, pLeft, rect.Y + rect.Height + 12);
ev.Graphics.DrawLine(Pens.Black, pLeft, rect.Y + rect.Height + tableFont.Height + 20, pWidth - pLeft, rect.Y + rect.Height + tableFont.Height + 20);
}
ev.Graphics.DrawString("制表人：" + worker + " 打印日期：" +DateTime.Now.Date, tableFont, drawBrush, pLeft, rect.Y + rect.Height + tableFont.Height + 20 + 5);
ev.HasMorePages = false;
printRecordComplete = 0;
return;
}
}
printingPageNumber += 1;
if (printingPageNumber >= pageNumber)
ev.HasMorePages = false;
else
{ ev.HasMorePages = true;
break;
}
}
}
#endregion
}
}