/// <summary>
/// 将ExDataGridView中的数据导出到Excel文件中
/// </summary>
/// <param name="gridview">要导出的ExDataGridView</param>
/// <returns></returns>
public static bool ExDataGridViewToExcel(ExDataGridView gridview)
{
//导出为xls格式用HSSF，xlsx用XSSF。
System.Windows.Forms.SaveFileDialog SFD = new System.Windows.Forms.SaveFileDialog();
string FileName = "";
if (gridview.Columns.Count == 0)
{
throw new Exception("没有可以导出的数据！");
}
SFD.Filter = "Excel文件(*.xls)|*.xls";
SFD.Title = "导出Excel表";
if (SFD.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return(false);
}
FileName = SFD.FileName.ToString().Trim();
try
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("数据页" + DateTime.Now.ToString("yyyyMMddHHmmssms"));
HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < gridview.Columns.Count; i++)
{
HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(gridview.Columns[i].HeaderText);
}
for (int i = 0; i < gridview.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < gridview.Columns.Count; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j);
if (gridview.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(gridview.Rows[i].Cells[j].Value));
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.String"))
{
cell.SetCellValue(gridview.Rows[i].Cells[j].Value.ToString());
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(gridview.Rows[i].Cells[j].Value));
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(gridview.Rows[i].Cells[j].Value));
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(gridview.Rows[i].Cells[j].Value));
}
else if (gridview.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(gridview.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
}
}
}
}
for (int i = 0; i < gridview.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream fs = new FileStream(FileName, FileMode.Create))
{
wb.Write(fs);
}
MessageBox.Show("导出成功！", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(true);
}
/// <summary>
/// 将ExDataGridView中的数据导出到Excel文件中
/// </summary>
/// <param name="gridview">要导出的ExDataGridView</param>
/// <returns></returns>
public static bool ExDataGridViewToExcel(ExDataGridView gridview)
{
System.Windows.Forms.SaveFileDialog SFD = new System.Windows.Forms.SaveFileDialog();
string FileName = "";
if (gridview.Columns.Count == 0)
{
throw new Exception("没有可以导出的数据！");
}
SFD.Filter = "Excel文件(*.xls)|*.xls";
SFD.Title = "导出Excel表";
if (SFD.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return(false);
}
FileName = SFD.FileName.ToString().Trim();
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
if (app == null)
{
return(false);
}
app.Visible = false;
app.Workbooks.Add();
Worksheet sheet1;
sheet1 = app.Worksheets["sheet1"];
object[,] RC = new object[gridview.Rows.Count + 1, gridview.Columns.Count];
int col = 0;
for (int i = 0; i < gridview.Columns.Count; i++)
{
//如果列头的名称为空则不导出这列
if (gridview.Columns[i].HeaderText.ToString().Length == 0 || gridview.Columns[i].Visible == false)
{
continue;
}
RC[0, col] = gridview.Columns[i].HeaderText.ToString();
string FormatString;
FormatString = gridview.Columns[i].DefaultCellStyle.Format;
if (FormatString.Trim().Length == 0)
{
FormatString = gridview.Columns[i].CellTemplate.Style.Format;
}
string ColumnType = "";
if (gridview.Columns[i].ValueType == null)
{
ColumnType = gridview.Columns[i].CellTemplate.ValueType.Name;
}
else
{
ColumnType = gridview.Columns[i].ValueType.Name;
}
switch (ColumnType)
{
case "Decimal":
case "Int32":
case "Int16":
case "Int":
case "Int64":
if (FormatString.Length > 0)
{
sheet1.Columns[col + 1].NumberFormatLocal = FormatString;
}
break;
case "String":
case "Boolean":
sheet1.Columns[col + 1].NumberFormatLocal = "@";
break;
case "DateTime":
sheet1.Columns[col + 1].NumberFormatLocal = "yyyy-M-d";
break;
}
col = col + 1;
}
for (int i = 0; i < gridview.Rows.Count; i++)
{
col = 0;
for (int j = 0; j < gridview.Columns.Count; j++)
{
if (gridview.Columns[j].HeaderText.ToString().Length != 0 && gridview.Columns[i].Visible == true)
{
RC[i + 1, col] = gridview.Rows[i].Cells[j].Value;
col = col + 1;
}
}
}
sheet1.Range[app.Cells[1, 1], app.Cells[gridview.Rows.Count + 1, gridview.Columns.Count]].Value2 = RC;
app.ActiveWorkbook.SaveCopyAs(FileName);
app.ActiveWorkbook.Saved = true;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(true);
}
