110,536
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
Mic_Gary 2013-09-04
- 打赏
- 举报
这有什么难的，第一列1，2，3反正是根据第二列数字来的，有规律可循，你就直接存第二列，然后第一列的split一下取到数据就行了
然后复制给你，你自己看吧
/// <summary>
/// datagridview 导出到Excel
/// </summary>
/// <param name="dataGridView1"></param>
/// <param name="title"></param>
/// <param name="time"></param>
public static void ExportExcel(DataGridView dataGridView1,string title,string time)
{
string saveFileName="";
SaveFileDialog saveDialog=new SaveFileDialog();
saveDialog.DefaultExt ="xls";
saveDialog.Filter = "Excel文件|*.xls|Excel文件|*.xlsx";
saveDialog.FileName ="导入记录查询结果 "+DateTime.Today.ToString("yyyy-MM-dd");
saveDialog.ShowDialog();
saveFileName=saveDialog.FileName;
if(saveFileName.IndexOf(":")<0) return; //被点了取消
ExportForDataGridview(dataGridView1, saveFileName, title,time);
if ( File.Exists(saveFileName))
System.Diagnostics.Process.Start(saveFileName);
}
public static bool ExportForDataGridview(DataGridView gridView, string fileName, string title, string time)
{
//建立Ｅｘｃｅｌ对象
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
return false;
}
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
int count = 4000;//一页数量
try
{
string sLen = "";
//取得最后一列列名
char H = (char)(64 + gridView.ColumnCount / 26);
char L = (char)(64 + gridView.ColumnCount % 26);
if (gridView.ColumnCount < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
int pages = 0;//记录第几页
if (worksheet == null)
{
return false;
}
//*******************设置输出格式******************************
//设置顶部説明
Range range = worksheet.get_Range(sLen + "1", "A1");
range.MergeCells = true;
range.RowHeight = 38;
range.Font.Bold = true;
range.Font.Size = 14;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.ColorIndex = 10;//字体颜色
app.ActiveCell.FormulaR1C1 = title;
//查询时间
range = worksheet.get_Range(sLen + "2", "A2");
range.NumberFormatLocal = '@';
range.MergeCells = true;
range.RowHeight = 26;
range.Font.Bold = true;
range.Font.Size = 12;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.ColorIndex = 10;//字体颜色
range.Value2 = time;
//标题
string sTmp = sLen + "3";
Range ranCaption = worksheet.get_Range(sTmp, "A3");
ranCaption.EntireColumn.AutoFit(); //自动调整列宽
ranCaption.HorizontalAlignment = XlHAlign.xlHAlignCenter;
string[] asCaption = new string[gridView.ColumnCount];
for (int i = 0; i < gridView.ColumnCount; i++)
{
asCaption[i] = gridView.Columns[i].HeaderText;
}
ranCaption.Value2 = asCaption;
//数据
//2000 一次复制的行数 有一定的数据要求 ··
object[,] obj = new object[2000,gridView.ColumnCount];
for (int r = 0; r < gridView.Rows.Count; r++)
{
for (int l = 0; l < gridView.ColumnCount; l++)
{
if (r % 2000 == 0 && r > 0&& l==0)//将内存中数据写入
{
worksheet.Cells.NumberFormat = "@";//将数字前面的0保留
string cell2 = sLen + ((int)(r-count*pages +3)).ToString();
string cell1 = "A" + ((int)(r - count * pages - 1996)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
ran.EntireColumn.AutoFit(); //自动调整列宽
//ran.Cells.NumberFormatLocal=
ran.Value2 = obj;
obj = new object[2000, gridView.ColumnCount];//置0
}
if (true)//是否分页
{
if (r % count == 0 && r > 0 && l == 0)//Excel分页
{
sheets.Add(Type.Missing,sheets[pages+1]);
pages = r / count;
worksheet = (_Worksheet)sheets.get_Item(pages+1);
//*******************设置输出格式******************************
//设置顶部説明
range = worksheet.get_Range(sLen + "1", "A1");
range.MergeCells = true;
range.RowHeight = 38;
range.Font.Bold = true;
range.Font.Size = 14;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.ColorIndex = 10;//字体颜色
app.ActiveCell.FormulaR1C1 = title;
//查询时间
range = worksheet.get_Range(sLen + "2", "A2");
range.MergeCells = true;
range.RowHeight = 26;
range.Font.Bold = true;
range.Font.Size = 12;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.ColorIndex = 10;//字体颜色
range.Value2 = time;
//标题
sTmp = sLen + "3";
ranCaption = worksheet.get_Range(sTmp, "A3");
ranCaption.EntireColumn.AutoFit(); //自动调整列宽
ranCaption.HorizontalAlignment = XlHAlign.xlHAlignCenter;
asCaption = new string[gridView.ColumnCount];
for (int i = 0; i < gridView.ColumnCount; i++)
{
asCaption[i] = gridView.Columns[i].HeaderText;
}
ranCaption.Value2 = asCaption;
}
}
obj[r%2000,l] = gridView.Rows[r].Cells[l].Value;//存储数据
if (r == gridView.RowCount - 1 && l == gridView.ColumnCount - 1)
{
worksheet.Cells.NumberFormat = "@";//将数字前面的0保留
string cell2 = sLen + ((int)(r - count * pages + 4)).ToString();
string cell1="A4";
if (r%4000>2000)
{
cell1 = "A2004";
}
Range ran = worksheet.get_Range(cell1, cell2);
//ran.Cells.NumberFormatLocal=
ran.Value2 = obj;
//ran.EntireColumn.AutoFill(ran);//清楚显示的方法！！//自动调整列宽
ran.EntireColumn.AutoFit();
obj = new object[2000, gridView.ColumnCount];//置0
}
}
}
//保存
// MessageBox.Show("dd");
}
catch (Exception)
{
}
finally
{
//关闭
app.UserControl = false;
workbook.SaveCopyAs(fileName);
workbook.Saved = true;
workbook.Close();
app.Quit();
}
return true;
}
晓风2013 2013-09-04
- 打赏
- 举报
谢谢，谢谢楼主的帮助！
