//导出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>
/// 将 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);
}
}
}