C#导出Excel问题
C#问题：把DataSet中的数据导出到Excel，怎么对Excel进行排版后再导出，如：自动排好A4的。还有就是怎么设置可以把导出的Excel每页都有标题。谢谢各位大虾，最好有源代码，如果回答好，还可以加分 --------------------编程问答-------------------- http://blog.csdn.net/yulinlover/archive/2009/02/15/3893943.aspx以上是excel与datagridview（相当于dataset）的交互 --------------------编程问答-------------------- 不好意思，我实在是看不懂
--------------------编程问答-------------------- 打开excel，赋值给单元格，设置格式
xlsheet.get_Range(xlsheet.Cells[1, 2], xlsheet.Cells[1, 8]).set_MergeCells(true);
xlsheet.get_Range(xlsheet.Cells[5, 1], xlsheet.Cells[5, 3]).set_MergeCells(true);
xlsheet.get_Range(xlsheet.Cells[5, 4], xlsheet.Cells[5, 6]).set_MergeCells(true);
xlsheet.get_Range(xlsheet.Cells[5, 7], xlsheet.Cells[5, 8]).set_MergeCells(true);
xlsheet.get_Range(xlsheet.Cells[ 1, 1], xlsheet.Cells[1, 14]).Font.set_Bold(true);
xlsheet.get_Range(xlsheet.Cells[ 1, 1], xlsheet.Cells[ 1, 14]).set_HorizontalAlignment(OWC10.XlHAlign.xlHAlignCenter);
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 14]).Font.set_Size(14);
xlsheet.get_Range(xlsheet.Cells[1, 8], xlsheet.Cells[1, 8]).set_ColumnWidth(20);
xlsheet.get_Range(xlsheet.Cells[ 1, 1], xlsheet.Cells[5, 8]).Borders.set_LineStyle(OWC10.XlLineStyle.xlContinuous);
http://topic.csdn.net/u/20091113/10/77a993dc-af9a-46a3-ab99-0c5050977d93.html --------------------编程问答-------------------- 我最想要解决的问题就是：在导出Excel后，打印的时候每页都有标题和表头，谢谢大家了 --------------------编程问答-------------------- 1.添加引用Excel
private void button1_Click(object sender, EventArgs e)
{
if (radioButton1 .Checked ==true )
{
this.PrintDataGridView(this.dataGridView1, true);
}
}
private bool PrintDataGridView(DataGridView dgv,bool isShowExcel )
{//导出到Excel
if (dgv.Rows.Count == 0)
{
return false;
}
Excel.Application excel=new Excel.Application(); //定义Excel对象
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcel;
for (int i = 0; i < dgv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText; //读取表头
}
for (int i = 0; i < dgv.RowCount - 1; i++) //读取DataGridView数据
{
for (int j = 0; j < dgv.ColumnCount; j++)
{ if (dgv[j,i].ValueType ==typeof (string ))
{ excel .Cells [i+2,j+1]=""+dgv[j,i].Value.ToString();
}
else
{excel .Cells [i+2,j+1]=dgv [j,i].Value .ToString ();
}
}
}
return false;
} --------------------编程问答--------------------
打印的A4纸，默认的最大行是25，你可以根据这个判断是不是需要到下一页，那么表头自己每页
写上不就行了。
一段打印程序：
protected override void OnPrintPage(PrintPageEventArgs e)
{
base.OnPrintPage(e);
int RowIndex;
int ColIndex;
float x = e.PageSettings.Margins.Left;
float y = e.PageSettings.Margins.Top;
Brush brush=new SolidBrush(Color.Black);
//vertical spacing between column header and first row
private const float HEADER_SPACING_Y = 15;
//By default, set to true. Unless the datagrid columns will exceed the available space
//on the current print page, we always reset the CurrentPrintRow variable.
_ResetPrintRow = true;
//output the column headers of grid
for (ColIndex = _CurrentPrintCol; ColIndex < = _DataGridTableStyle.GridColumnStyles.Count-1; ColIndex++)
{
//Check to see if the next column will exceed the max available space on the current print page.
//If true, set the ResetPrintRow switch to false and break from the loop.
if ((x + _DataGridTableStyle.GridColumnStyles[ColIndex].Width) > = e.MarginBounds.Right)
{
_ResetPrintRow = false;
break;
}
else
{
string HeaderText=_DataGridTableStyle.GridColumnStyles[ColIndex].HeaderText;
e.Graphics.DrawString(HeaderText, _ColumnHeaderFont, brush, x, y);
x += _DataGridTableStyle.GridColumnStyles[ColIndex].Width;
}
}
//output the rows of datagrid
int i=0;
for (RowIndex = _CurrentPrintRow; RowIndex < = _DataRowCount-1; RowIndex++)
{
//reset new row x to left margin value
x = e.PageSettings.Margins.Left;
//Calculate y location for new row
y = e.PageSettings.Margins.Top + (HEADER_SPACING_Y + (i * _RecordFont.GetHeight(e.Graphics)));
//Check that the next row will not exceed the max available space on the current print page
if (y > = e.MarginBounds.Bottom)
break;
else //loop and print columns of the current row
{
for (ColIndex = _CurrentPrintCol; ColIndex < = _DataGridTableStyle.GridColumnStyles.Count-1; ColIndex++)
{
//Check that the next column will exceed the max available space on the current print page.
//If true, exit for and set ResetPrintRow switch to False.
if ((x + _DataGridTableStyle.GridColumnStyles[ColIndex].Width) > = e.MarginBounds.Right)
{
_ResetPrintRow = false;
break;
}
else //print on current page
{
string CellText="";
if (_DataGrid[RowIndex, ColIndex] != DBNull.Value) //check for null value
//get the current cell contents - replace any carriage line returns with empty space.
CellText=_DataGrid[RowIndex, ColIndex].ToString().Replace(Environment.NewLine," ");
e.Graphics.DrawString(CellText, _RecordFont, brush, x, y);
x += _DataGridTableStyle.GridColumnStyles[ColIndex].Width;
}
}
i += 1;
}
}
if (_ResetPrintRow==true)
{
//hold the last row we printed
_CurrentPrintRow = RowIndex;
//reset the print col back to the first column
_CurrentPrintCol = 0;
}
else
//hold the last column we printed
_CurrentPrintCol = ColIndex;
//Check if additional pages are to be printed
if (_CurrentPrintRow == _DataRowCount && _CurrentPrintCol == 0)
e.HasMorePages = false;
else
e.HasMorePages = true;
}
--------------------编程问答-------------------- mark
--------------------编程问答--------------------
给楼主顶一个
--------------------编程问答-------------------- 你用打印机A4纸试试。在DataSet里增加每页的表头行。
DataRow dr = ds.Tables[0].NewRow();
dr["字段"] = "值";
ds.Tables[0].Rows.Add(dr);
再导出。否则就要使用Excel API了。 --------------------编程问答-------------------- 解决一个问题有很多途径，我说说我对excel格式操作的思路，希望对LZ有帮助。
excel的格式操作很多很复杂，想在自己写的程序里，一一实现不仅代码量大还容易出错，为何不提前用excel做好一个模板，程序只负责读、写，操作岂不要简单容易些？当然具体情况具体分析，不到万不得已，尽量不要在程序中对excel进行格式操作。 --------------------编程问答--------------------
--------------------编程问答--------------------
protected override void OnPrintPage(PrintPageEventArgs e)
{
base.OnPrintPage(e);
int RowIndex;
int ColIndex;
float x = e.PageSettings.Margins.Left;
float y = e.PageSettings.Margins.Top;
Brush brush=new SolidBrush(Color.Black);
//vertical spacing between column header and first row
private const float HEADER_SPACING_Y = 15;
//By default, set to true. Unless the datagrid columns will exceed the available space
//on the current print page, we always reset the CurrentPrintRow variable.
_ResetPrintRow = true;
//output the column headers of grid
for (ColIndex = _CurrentPrintCol; ColIndex < = _DataGridTableStyle.GridColumnStyles.Count-1; ColIndex++)
{
//Check to see if the next column will exceed the max available space on the current print page.
//If true, set the ResetPrintRow switch to false and break from the loop.
if ((x + _DataGridTableStyle.GridColumnStyles[ColIndex].Width) > = e.MarginBounds.Right)
{
_ResetPrintRow = false;
break;
}
else
{
string HeaderText=_DataGridTableStyle.GridColumnStyles[ColIndex].HeaderText;
e.Graphics.DrawString(HeaderText, _ColumnHeaderFont, brush, x, y);
x += _DataGridTableStyle.GridColumnStyles[ColIndex].Width;
}
}
//output the rows of datagrid
int i=0;
for (RowIndex = _CurrentPrintRow; RowIndex < = _DataRowCount-1; RowIndex++)
{
//reset new row x to left margin value
x = e.PageSettings.Margins.Left;
//Calculate y location for new row
y = e.PageSettings.Margins.Top + (HEADER_SPACING_Y + (i * _RecordFont.GetHeight(e.Graphics)));
//Check that the next row will not exceed the max available space on the current print page
if (y > = e.MarginBounds.Bottom)
break;
else //loop and print columns of the current row
{
for (ColIndex = _CurrentPrintCol; ColIndex < = _DataGridTableStyle.GridColumnStyles.Count-1; ColIndex++)
{
//Check that the next column will exceed the max available space on the current print page.
//If true, exit for and set ResetPrintRow switch to False.
if ((x + _DataGridTableStyle.GridColumnStyles[ColIndex].Width) > = e.MarginBounds.Right)
{
_ResetPrintRow = false;
break;
}
else //print on current page
{
string CellText="";
if (_DataGrid[RowIndex, ColIndex] != DBNull.Value) //check for null value
//get the current cell contents - replace any carriage line returns with empty space.
CellText=_DataGrid[RowIndex, ColIndex].ToString().Replace(Environment.NewLine," ");
e.Graphics.DrawString(CellText, _RecordFont, brush, x, y);
x += _DataGridTableStyle.GridColumnStyles[ColIndex].Width;
}
}
i += 1;
}
}
if (_ResetPrintRow==true)
{
//hold the last row we printed
_CurrentPrintRow = RowIndex;
//reset the print col back to the first column
_CurrentPrintCol = 0;
}
else
//hold the last column we printed
_CurrentPrintCol = ColIndex;
//Check if additional pages are to be printed
if (_CurrentPrintRow == _DataRowCount && _CurrentPrintCol == 0)
e.HasMorePages = false;
else
e.HasMorePages = true;
}
谢谢，虽然你没有给出一行代码，但是说到了问题的精髓，我使用模板就可以很简单就搞定了 --------------------编程问答-------------------- 楼主如果需要一个脱离Excel环境的方法的话，可以参考一下
NPOI - 一个能帮助你直接读写office文件流的库
--------------------编程问答--------------------
呵呵，刚想回复你的
不过还是恭喜LZ解决了问题
我就贴个链接好了~
http://topic.csdn.net/u/20100325/18/e1cc3263-aa1c-4ae7-89c7-5f44a35513db.html
补充：.NET技术 , C#
