//导出Excel文件
public static void DataToExcel(DataGridView dgvPara)
{
int intColIndex = 1; //列序号
int intRowCount = dgvPara.RowCount; //行数
int intColCount = dgvPara.ColumnCount; //列数
int intNotShowCount = 0; //不显示的列
object[,] objData; //保存DataGridView中的数据
string strFileName = "";
//判断:如果行数或者列数有问题,则不予导出
//行数列数必须大于0
if (intRowCount <= 0 || intColCount <= 0)
{
MessageBox.Show("没有数据可供保存!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//行数不可以大于65536
if (intRowCount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536行),不能保存!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//列数不可以大于255
if (intColCount > 255)
{
MessageBox.Show("数据记录列数太多(最多不能超过255列),不能保存!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//初始化Excel
Microsoft.Office.Interop.Excel.Application AppExcel = new Microsoft.Office.Interop.Excel.Application();
if (AppExcel == null)
{
MessageBox.Show("Excel无法启动!");
return;
}
AppExcel.Visible = false;
//初始化Sheet
Workbooks MyWorkBooks = AppExcel.Workbooks;
_Workbook MyWorkBook = MyWorkBooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets MySheets = AppExcel.Sheets;
_Worksheet MySheet = (_Worksheet)MySheets[1];
if (MySheet == null)
{
MessageBox.Show("WorkSheet错误!");
return;
}
//初始化Range
Range MyRange = MySheet.Range["A1"];
if (MyRange == null)
{
MessageBox.Show("Range错误!");
return;
}
try
{
//填写列表头
foreach (DataGridViewColumn col in dgvPara.Columns)
{
if (col.Visible == true && col.HeaderText != "")
{
if (col.HeaderText.ToString().Contains("日期") || col.HeaderText.ToString().Contains("时间"))
{
MySheet.Range[MySheet.Cells[1, intColIndex], MySheet.Cells[intRowCount + 1, intColIndex]].NumberFormatLocal = "yyyy-MM-dd";
}
MySheet.Cells[1, intColIndex++] = col.HeaderText;
}
else
{
intNotShowCount++;
}
}
//保存DataGridView中的数据到objData中
objData = new object[intRowCount + 1, intColCount];
for (int i = 0; i < intRowCount; i++)
{
for (int j = 0, k = 0; j < intColCount; j++)
{
if (dgvPara.Columns[j].Visible == false || dgvPara.Columns[j].HeaderText == "")
continue;
if (dgvPara.Rows[i].Cells[j].Value == null)
objData[i, k] = "";
else
objData[i, k] = dgvPara.Rows[i].Cells[j].Value;
k++;
}
}
格式化数据列
//intRowCount++;
//MySheet.get_Range("A2", "A" + intRowCount.ToString()).NumberFormatLocal = "@";
//MySheet.get_Range("B2", "B" + intRowCount.ToString()).NumberFormatLocal = "yyyy-M-d";
//MySheet.get_Range("C2", "C" + intRowCount.ToString()).NumberFormatLocal = "@";
//把数据填充到Excel文件中
MyRange = MySheet.Range[MySheet.Cells[2, 1], MySheet.Cells[intRowCount + 1, intColCount-intNotShowCount]];
MyRange.Value2 = objData;
MySheet.Columns.AutoFit();
//画边框
MyRange = MySheet.Range[MySheet.Cells[1, 1], MySheet.Cells[intRowCount + 1, intColCount - intNotShowCount]];
//MyRange.Cells.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
MyRange.Borders.LineStyle = 1;
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 97-2003 工作薄(*.xls)|*.xls|Excel 工作薄(*.xlsx)|*.xlsx";
sfd.RestoreDirectory = true;
sfd.OverwritePrompt = true;
sfd.DefaultExt = ".xls";
sfd.Title = "保存文件";
if (DialogResult.OK != sfd.ShowDialog())
{
MySheet=null;
return;
}
strFileName = sfd.FileName;
if (System.IO.File.Exists(strFileName))
{
System.IO.File.Delete(strFileName);
}
MyWorkBook.SaveAs(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
if (MessageBox.Show("保存EXCEL成功,是否要打开文件?", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
AppExcel.Visible = true;
}
}
catch (Exception err)
{
MessageBox.Show(err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
if (AppExcel.Visible == false)
{
MyWorkBook.Close(null, null, null);
MyWorkBooks.Close();
AppExcel.Application.Quit();
AppExcel.Quit();
MySheet = null;
MyWorkBook = null;
AppExcel = null;
}
}
}
DataGridView导出excel
最新推荐文章于 2020-05-10 15:55:36 发布