//导出Excel
private void button1_Click(object sender, EventArgs e)
{
ClsExcel cExcel = null;
cExcel = new ClsExcel();
string fileName = "人员列表_" + DateTime.Now.ToString("yyyyMMddHHmmss"); //Excel文件名
string sheetName = "全体人员"; //sheet页的名称
cExcel.ExportToExcel(ref dataGridView1, fileName, sheetName);
cExcel.Close(false);
}
/// <summary>
/// 打印Excel文档
/// </summary>
/// <param name="excel"></param>
public void PrintOut(ClsExcel excel)
{
int iCounter = excel.WorkSheetCount();
for (int i = 1; i <= iCounter; i++)
{
excel.SetActiveSheet(i);
excel.PrintOut();
}
excel.Close(false);
}
/// <summary>
/// 打印Excel文档
/// </summary>
/// <param name="excel"></param>
/// <param name="isLandcape">是否横向打印 true 为是</param>
public void PrintOut(ClsExcel excel, bool isLandcape)
{
int iCounter = excel.WorkSheetCount();
for (int i = 1; i <= iCounter; i++)
{
excel.SetActiveSheet(i);
if (true == isLandcape)
{
xSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
}
excel.PrintOut();
}
excel.Close(false);
}
/// <summary>
/// 格式化空白行
/// </summary>
/// <param name="cExcel"></param>
/// <param name="rowIndex">行索引</param>
/// <param name="rowHeight">行高</param>
/// <param name="border">边框样式</param>
public void FormatBlank(ClsExcel cExcel, int rowIndex, double rowHeight, int border)
{
Excel.Worksheet xSheet = cExcel.X_Sheet;
Excel.Range xRange;
try
{
xRange = (Excel.Range)xSheet.get_Range(xSheet.Cells[rowIndex, 1],
xSheet.Cells[rowIndex, 1]);
xRange.RowHeight = rowHeight;
xRange.Borders.LineStyle = border;
}
catch (Exception ex)
{
throw (new Exception("[FormatBlank]:" + ex.Message));
}
finally
{
xRange = null;
}
}
/// <summary>
/// 格式化列标题
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="dt"></param>
//public void FormatHeadText(int rowIndex, int colIndex)
//{
// Excel.Range xRange;
// try
// {
// xRange = (Excel.Range)xSheet.get_Range(xSheet.Cells[rowIndex, 1],
// xSheet.Cells[rowIndex, colIndex]);
// xSheet.Columns.AutoFit();
// xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 居中对齐
// xRange.Font.Bold = true;
// xRange.WrapText = true;
// }
// catch (Exception e)
// {
// throw (new Exception("SetTextFormat:" + e.Message));
// }
// finally
// {
// xRange = null;
// }
//}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="cExcel"></param>
/// <param name="beginRowIndex">起始行索引</param>
/// <param name="beginColIndex">起始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColIndex">结束列索引</param>
public void FormatMergeCell(ClsExcel cExcel, int beginRowIndex, int beginColIndex,
int endRowIndex, int endColIndex)
{
Excel.Worksheet xSheet = cExcel.X_Sheet;
Excel.Range xRange;
try
{
xRange = (Excel.Range)xSheet.get_Range(xSheet.Cells[beginRowIndex, beginColIndex],
xSheet.Cells[endRowIndex, endColIndex]);
xRange.MergeCells = true;
}
catch (Exception ex)
{
throw new Exception("[FormatMergeCell]:" + ex.Message);
}
finally
{
xRange = null;
}
}
/// <summary>
/// 将 DataGridView 中的数据导出到Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="rowBeginNum"></param>
/// <param name="colBeginNum"></param>
/// <param name="fileName"></param>
public static void ExportDataGridView2Excel(ref DataGridView dgv, ref DataGridView dgv2, int rowBeginNum,
int colBeginNum, string fileName, string frmBarName, string sheet1Name, string sheet2Name)
{
if (null == fileName || 0 == fileName.Length)
{
return;
}
string openPeriodOne = sheet1Name + "以后未返校学生列表";
string openPeriodTwo = sheet2Name + "以后未返校学生列表";
int rowNum = dgv.Rows.Count;
int columnNum = dgv.Columns.Count;
int rowIndex = 1 + rowBeginNum;
int columnIndex = colBeginNum;
int rowNum2 = dgv2.Rows.Count;
int columnNum2 = dgv2.Columns.Count;
int rowIndex2 = 1 + rowBeginNum;
int columnIndex2 = colBeginNum;
ClsExcel cExcel = null;
//FrmProgressBar fmPB = new FrmProgressBar();
try
{
//fmPB = ClsPubFuctions.GetProgressBarForm(frmBarName, (rowNum + columnNum + rowNum2 + columnNum2));
//fmPB.Show();
//Excel.Application.DoEvents();
//ClsSystem.mainForm.Cursor = Cursors.WaitCursor;
cExcel = new ClsExcel();
// 复制Sheet
cExcel.CopySheet(1, true);
cExcel.SetActiveSheet(1);
#region --openPeriodOne--
for (int j = 0; j < columnNum; j++)
{
if (!dgv.Columns[j].Visible || dgv.Columns[j].Name == "Check")
{
continue;
}
columnIndex++;
cExcel.SetValue(rowIndex, columnIndex, dgv.Columns[j].HeaderText);
if (0 != dgv.Rows.Count && null != dgv.Rows[0].Cells[j].Value)
{
cExcel.FormatHeadText(1, columnNum);
}
}
// 行循环
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++) // 列循环
{
if (!dgv.Columns[j].Visible || dgv.Columns[j].Name == "Check")
{
continue;
}
columnIndex++;
if (null != dgv.Rows[i].Cells[j].Value)
{
cExcel.SetValue(rowIndex, columnIndex, dgv.Rows[i].Cells[j].Value.ToString().Replace("\r\n", ""));
}
}
//fmPB.ProgressBar.Value += 1;
}
cExcel.X_Sheet.Name = openPeriodOne;
#endregion
// -------------------------------------以上为openPeriodOne-----------------------------
#region --openPeriodTwo--
cExcel.SetActiveSheet(2);
for (int j = 0; j < columnNum2; j++)
{
if (!dgv2.Columns[j].Visible || dgv2.Columns[j].Name == "Check")
{
continue;
}
columnIndex2++;
cExcel.SetValue(rowIndex2, columnIndex2, dgv2.Columns[j].HeaderText);
if (0 != dgv2.Rows.Count && null != dgv2.Rows[0].Cells[j].Value)
{
cExcel.FormatHeadText(1, columnNum2);
}
//fmPB.ProgressBar.Value += 1;
}
// 行循环
for (int i = 0; i < rowNum2; i++)
{
rowIndex2++;
columnIndex2 = 0;
for (int j = 0; j < columnNum2; j++) // 列循环
{
if (!dgv2.Columns[j].Visible || dgv2.Columns[j].Name == "Check")
{
continue;
}
columnIndex2++;
if (null != dgv2.Rows[i].Cells[j].Value)
{
cExcel.SetValue(rowIndex2, columnIndex2, dgv2.Rows[i].Cells[j].Value.ToString().Replace("\r\n", ""));
}
}
//fmPB.ProgressBar.Value += 1;
}
cExcel.X_Sheet.Name = openPeriodTwo;
#endregion
cExcel.xWorkBook.SaveCopyAs(fileName);
//ClsSystem.mainForm.Cursor = Cursors.Default;
}
catch (Exception ex)
{
MessageBox.Show("ExportDataGridView2Excel:" + ex.ToString());
}
finally
{
//if (fmPB != null)
//{
// fmPB.Close();
//}
//ClsSystem.mainForm.Cursor = Cursors.Default;
if (cExcel != null)
{
cExcel.Close(false);
}
}
}