Question

c#打印报表可自定义表格样式，可分页打印

我是这么打印的，但是没有标题，也不能自定义表格样式
private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
{

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=LUKE\\SQLEXPRESS;uid=sa;pwd=sa;database=luke";
conn.Open();
DataTable dt = new DataTable("resouce");
dt.Clear();
string sql = "";
if (textBox1.Text == "" && textBox2.Text == "")
{
sql = "select * from text1";
label1.Text = sql;
}
else
{
sql = "select * from text1 where bh like '%" + textBox1.Text + "%'";
label1.Text = sql;
}
SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
adp.Fill(dt);
dataGridView1.DataSource = dt.DefaultView;

int r = 0;
int c = 20;
for (int i = 0; i < dt.Rows.Count; i++)
{

for (int j = 0; j < dt.Columns.Count; j++)
{

e.Graphics.DrawString(dt.Rows[i][j].ToString(), new Font("宋体", 72, FontStyle.Regular), Brushes.Black, r, c);
r = r + 160;

}
r = 0;
c += 200;

}

谁能帮帮我

Answer 1

你是要把dataGridView导出到excel，并且设置excel格式是吧，我写了2个方法

一个是输出到excel的，一个是改excel格式的(如果文件内容过大，改格式比燃盯较费时)

你拿去调用吧，根据需要修改格式，各个属性后面加了备注

• 方法：

        /// <summary>导出1个dataGridView到excel
        /// 
        /// </summary>
        /// <param name="fileName">导出Excel文件名</param>
        /// <param name="sheetName">sheet名</param>
        /// <param name="dataGridView1"></param>
        public static void ExportToExcel(string fileName, string sheetName, DataGridView dataGridView1)
        {
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = 毁租"Excel file|*.xls";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消后不会报错

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
          皮余和  {
                MessageBox.Show("Unable to create the Excel object, may be your PC did not install Excel");
                return;
            }
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");  //我这是英文的，你可以改为中文的en-ZH    
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

            #region 创建sheet1
            Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

            //冻结首行
            xlApp.ActiveWindow.SplitRow = 1;
            xlApp.ActiveWindow.SplitColumn = 0;
            xlApp.ActiveWindow.FreezePanes = true;

            if (dataGridView1.RowCount > 1)
            {
                //写入标题
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                }
                //写入数值
                for (int r = 0; r < dataGridView1.Rows.Count; r++)
                {
                    for (int i = 0; i < dataGridView1.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
                    }
                    System.Windows.Forms.Application.DoEvents();
                }
                //excel单元格格式设置
                SetExcelFormat(worksheet1, dataGridView1.Rows.Count, dataGridView1.Columns.Count);  //其他设置
            }
            else
            {
                worksheet1.Cells[1, 1] = "No records";
            }
            worksheet1.Name = sheetName;
            #endregion

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //fileSaved = true;
                }
                catch (Exception ex)
                {
                    //fileSaved = false;
                    MessageBox.Show("An error occurred when export file, the file likely is opening！\n" + ex.Message);
                }

            }

            xlApp.Quit();
            GC.Collect();//强行销毁 
            MessageBox.Show("Saved success !", "Export Result", MessageBoxButtons.OK);
        }
        
        
        /// <summary>设置sheet的格式
        /// 
        /// </summary>
        /// <param name="worksheet">工作簿sheet</param>
        /// <param name="rowCount">sheet表行数</param>
        /// <param name="columnCount">sheet表列数</param>
        public static void SetExcelFormat(Worksheet worksheet, int rowCount, int columnCount)
        {
            //设置列名格式：
            try
            {
                Microsoft.Office.Interop.Excel.Range titleRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]); //选取单元格，第一行    Exception from HRESULT: 0x800A03EC

                if (titleRange.Text != null)
                {
                    //range1.Merge(true);//合并单元格
                    //range1.Value2 = strTitle; //设置单元格内文本
                    titleRange.Font.Name = "Arial";//设置字体
                    titleRange.Font.Size = 11;//字体大小
                    titleRange.Font.Bold = true;//加粗显示
                    titleRange.Interior.ColorIndex = 8; //标题颜色
                    titleRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//水平居中
                    titleRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//垂直居中
                    titleRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//设置边框
                    titleRange.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框常规粗细
                    titleRange.Interior.Color = Color.FromArgb(0, 255, 255);//设置颜色
                }
            }
            catch (Exception)
            {
                MessageBox.Show("At least one sheet is empty");
                return;
            }
            //设置内容格式：
            for (int i = 1; i <= columnCount; i++)
            {
                Microsoft.Office.Interop.Excel.Range contentRange = worksheet.get_Range(worksheet.Cells[2, i], worksheet.Cells[rowCount + 1, i]);   //选取其他单元格
                contentRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;//水平居左
                contentRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;//垂直居上
                contentRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//设置边框
                contentRange.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框常规粗细
                contentRange.RowHeight = 100;    //行高
                contentRange.WrapText = true;   //自动换行
                contentRange.NumberFormatLocal = "@";//文本格式
                contentRange.Font.Name = "Arial";
                contentRange.Font.Size = 11;
                contentRange.EntireColumn.AutoFit();
                contentRange.Rows.Interior.Color =Color.FromArgb(0, 255, 255);    //单元格颜色
            }
        }

• 调用：

        private void button1_Click(object sender, EventArgs e)
        {
            string fileName = "Excel名_" + DateTime.Now.ToString("yyyyMMdd");
            string sheetName = "sheet名";
            ExportToExcel(fileName, sheetName, dataGridView1);
        }