{
MessageBox.Show("没有要导出的数据");
return;
}
// if (dataGridView1.Rows.Count == 0)
// return;
// //实例化一个Excel.Application对象
// Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// //让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写
// excel.Visible = false;
// //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错
//excel.Application.Workbooks.Add(true);
// //生成Excel中列头名称
// for (int i = 0; i < dataGridView1.Columns.Count; i++)
// {
// excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
// }
// //把DataGridView当前页的数据保存在Excel中
// for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
// {
// for (int j = 0; j < dataGridView1.Columns.Count; j++)
// {
// if (dataGridView1[j, i].ValueType == typeof(string))
// {
// excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
// }
// else
// {
// excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
// }
// } //inner for
// } //out for
// //设置禁止弹出保存和覆盖的询问提示框
// excel.DisplayAlerts = false;
// excel.AlertBeforeOverwriting = false;
// //保存工作簿
// excel.Application.Workbooks.Add(true).Save();
// //保存excel文件
// excel.Save("D:" + "\\KKHMD.xls");
// //确保Excel进程关闭
// excel.Quit();
// excel = null;
//建立Excel对象
//-***************获取excel对象***************
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
MessageBox.Show("无法启动,可能你的机器上没有安装Excel!", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(true);
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
string oldCaption = DPStart.Value.Month.ToString();
//列索引,行索引,总列数,总行数
//int colIndex = 0;
//int rowIndex = 0;
int colCount = this.dataGridView2.Columns.Count;
int rowCount = dataGridView2.Rows.Count;
//string sPath = @"C:\Inetpub\wwwroot\test_20110222\test\Report\";
//string sFileName = "test_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//sPath += sFileName;
//System.IO.File.Copy(@"C:\Inetpub\wwwroot\test_20110222\test\Report\test.xls", sPath, true);
string strpth;//打开EXCEL已有的格式文件路径.
strpth = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
strpth += @"blank.xls";
//打开或者创建Excel文件
Microsoft.Office.Interop.Excel.Workbook eBookWr = app.Workbooks.Open(strpth, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)eBookWr.Worksheets[1];
//Excel.Workbook eBookWr = eAppWr.Workbooks.Add(1);
//添加Sheet
//mySheet = (Worksheet)MyBook.Worksheets[1];
//app.Cells[10, 1] = "dfvb";
//Microsoft.Office.Interop.Excel.Worksheet eSheetWr = (Microsoft.Office.Interop.Excel.Worksheet)eBookWr.Worksheets.Add( Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Sheet重命名
//eSheetWr.Name = strSheetName;
//循环,写入数据,rowindex,colindex都是从1开始
//SheetWr.cells[rowindex,colindex]=strValue;
//循环结束
//methord one
// *****************获取数据*********************
// 创建缓存数据
//object[,] objData = new object[rowCount + 3, colCount];
//// 创建列标题
//foreach (DataGridViewColumn col in dataGridView1.Columns)
//{
// objData[rowIndex, colIndex++] = col.HeaderText;
//}
//// 获取具体数据
//for (rowIndex = 1; rowIndex < rowCount; rowIndex++)
//{
// for (colIndex = 0; colIndex < colCount; colIndex++)
// if ( dataGridView1.Rows[rowIndex - 1].Cells[colIndex]. ValueType == typeof(DateTime))
// {
// objData[rowIndex, colIndex] = dataGridView1.Rows[rowIndex - 1].Cells[colIndex].Value.ToString();
// }
// else
// {
// objData[rowIndex, colIndex] = dataGridView1.Rows[rowIndex - 1].Cells[colIndex].Value;
// }
// //{
// // objData[rowIndex, colIndex] = dataGridView1.Rows[rowIndex - 1].Cells[colIndex].Value;
// //}
//}
//objData[rowCount + 1, 0] = "合 计:";// rowCount.ToString();
//objData[rowCount + 1, 1] = rowCount.ToString();
////********************* 写入Excel*******************
//range = worksheet.get_Range(app.Cells[2, 1], app.Cells[rowCount + 3, colCount]);
//range.Value2 = objData;
//生成Excel中列头名称
//for (int i = 0; i < 5; i++)
//string a = "no";
//app.Cells[2, 0]. = a.Trim ();
//注意:DATAGRIDEVIW中的列行都是以0开始的.而EXCEL中的单元序号是以1开始
//perfect
//app.Cells[2, 1] = "序号";
//for (int i = 2; i < dataGridView2.Rows.Count + 1; i++)
//{
// app.Cells[i + 1, 1] = i - 1;
//}
//for (int i = 1; i < dataGridView1.Columns.Count + 2; i++)
//{
// app.Cells[2, i + 1] = dataGridView2.Columns[i - 1].HeaderText;
// //string bb = dataGridView1.Columns[i].HeaderText;
//}
// //把DataGridView当前页的数据保存在Excel中
for (int i =0; i < dataGridView2.Rows.Count - 1; i++)
{
for (int j = 1; j < dataGridView2 .Columns .Count+1; j++)
{
if (dataGridView2[j - 1, i].ValueType == typeof(DateTime))
{
range = worksheet.get_Range(app.Cells[i +3, j], app.Cells[i + 3, j]);
range.NumberFormatLocal = "@";
app.Cells[i + 3, j ] = dataGridView2[j - 1, i].Value.ToString();
}
else//if ((left == null) || (right == null))
// if ((dataGridView1[j, i].ValueType == typeof(int)) || (dataGridView1[j, i].ValueType == typeof(double)))
//string stringType = "12345";
//object objectType = "54321";
//int intType = Convert.ToInt32(longType); //正确
//int intType = Convert.ToInt32(stringType); //正确
//int intType = Convert.ToInt32(objectType); //正确
//int intType = Convert.ToInt32(dataGridView1[j, i].Value);
//if (( Convert.ToInt32(dataGridView1.Rows [i].Cells e[j] .Value))>0)
//{
// range = worksheet.get_Range(app.Cells[i + 3, j + 1], app.Cells[i + 3, j + 1]);
// range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
// app.Cells[i + 3, j + 1] = dataGridView1[j, i].Value.ToString();
//}
//else
//{
app.Cells[i +3, j ] = dataGridView2[j - 1, i].Value.ToString();
//}
} //inner for
} //out for
//app.get_Range("d3", app.Cells[3, rowCount]).Calculate();
//worksheet.Columns.AddComment("a");
worksheet.Columns.EntireColumn.AutoFit();//自动适应每列的宽
//worksheet.Rows.EntireRow.AutoFit();
//worksheet.Cells.EntireColumn.AutoFit();
//range.Borders.LineStyle = 1;
//changed
//System.Windows.Forms.Application.DoEvents();
//range = worksheet.get_Range(app.Cells[2, 1], app.Cells[rowCount + 1, colCount + 1]);
//range.Borders.LineStyle = 1;
////********************* 设置输出格式****************
////设置顶部说明
//range = worksheet.get_Range(app.Cells[1, 1], app.Cells[1, colCount + 1]);
//range.MergeCells = true;
//range.RowHeight = 38;
//range.Font.Bold = true;
//range.Font.Size = 14;
////range.Font.ColorIndex = 10;
//range.Borders.LineStyle = 1;
//app.ActiveCell.FormulaR1C1 = "奥昌皮革" + oldCaption + "月份" + comboBox2.Text.Trim();
;
//特殊数据格式
//range = worksheet.get_Range(app.Cells[2, 1], app.Cells[rowCount, colCount]);
//range.NumberFormat = "yyyy-MM-dd hh:mm:ss";
//app.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
//range = worksheet.get_Range(app.Cells[2, 1], app.Cells[2, colCount + 1]);
////range.Interior.ColorIndex = 10;
//range.Font.Bold = true;
//range.RowHeight = 20;
//range.Borders.LineStyle = 1;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 1]).ColumnWidth = 8;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 2]).ColumnWidth = 20;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 3]).ColumnWidth = 10;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 4]).ColumnWidth = 10;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 5]).ColumnWidth = 10;
//app.ActiveSheet.Range("A1:G1").Merge();
//app.ActiveSheet.PageSetup.Orientation = 2;//设置为横向打印
////设置合计为右对齐并加粗
//range = worksheet.get_Range(app.Cells[rowCount + 2, 1], app.Cells[rowCount + 4, colCount]);
//range.Borders.LineStyle = 0;
//最后两行的设置
//string a = "";
//a = app.get_Range(app.Cells[3, 6], app.Cells[rowCount, 6]).Calculate().ToString();
//加载一个合计行
//
//int rowsum = rowindex + 1;
//int colsum = 2;
//app.Cells[rowCount + 3, 2] = "合计";
//app.get_Range(app.Cells[rowCount, 2], app .Cells [rowCount, 2]).horizontalalignment = excel.xlhalign.xlhaligncenter;
//
//设置选中的部分的颜色
////
//app.get_Range(app.Cells[rowCount , colCount ],app.Cells[rowCount , colCount ] ).Select ();
//app.get_Range(app.Cells[rowCount, colCount], app.Cells[rowCount, colCount]).Interior.Color = 19;//设置为浅黄色,共计有56种
//
//app.Cells[rowCount + 2, 2] = app.get_Range(app.Cells[3, 6], app.Cells[rowCount, 6]).Calculate().ToString();
//app.Cells[rowCount + 3, 2] = "dfd";
//range = worksheet.get_Range(app.Cells[2, 2], app.Cells[rowCount, 2]);
//range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
//range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
//range = worksheet.get_Range(app.Cells[rowCount + 3, 1], app.Cells[rowCount + 3, 1]);
//range.Font.Bold = true;
//range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
////设置合计值为左对齐
//range = worksheet.get_Range(app.Cells[rowCount + 3, 2], app.Cells[rowCount + 3, 2]);
//range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
//写入完成后将有数据的范围内设置其边框与内部线条
//Microsoft.Office.Interop.Excel.ApplicationClass App = null;
//Microsoft.Office.Interop.Excel.Workbook wb = null;
//边框与内部线条
worksheet.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = 3;
worksheet.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = 2;
worksheet.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = 3;
worksheet.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = 3;
worksheet.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = 2;//内部线条
worksheet.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = 2;
//*********************** 保存 *********************
try
{
TimeSpan dateBegin = new TimeSpan(DateTime.Now.Ticks);
SaveFileDialog file = new SaveFileDialog();
file.DefaultExt = "xls";
file.Filter = "Excel文件|*.xls";
//file.FilterIndex = 1;
string saveFilePath = "";
if (file.ShowDialog() == DialogResult.OK)
{
saveFilePath = file.FileName;
}
else
{
return;
}
//app.Save(saveFilePath);//会多出一个SHEET的保存对话框
// app.Visible = true;
//workbook.Saved = true;
//workbook.SaveCopyAs(saveFilePath);
eBookWr.Saved = true;
eBookWr.SaveCopyAs(saveFilePath);
//eBookWr.Sheets.Delete();
}
catch (Exception ex)
{
MessageBox.Show("保存文件出错:" + ex.Message);
}
// *********************释放 * ***********************
app.Quit();
GC.Collect();
//KillSpecialExcel(app); //杀任何进程(EXCEL里的)
评论