/// <summary>
///导出数据为Excel
/// </summary>
/// <param name="dataGridViewSource">需要导出的数据</param>
/// <param name="saveFileName">导出文件的名称</param>
/// <returns></returns>
public static string ImprotDataToExcel(DataGridView dataGridViewSource, string saveFileName)
{
//string DownLoadPath = System.IO.Directory.GetCurrentDirectory() + "\\template\\模版.xlsx";
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel文件 (*.xlsx)|*.xlsx|(*.xls)|*.xls";
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.RestoreDirectory = true;
//获取时分秒
string exportDate = DateTime.Now.ToString("yyyyMMddHHmmss");
//名称后面自动加入时分秒
saveFileDialog1.FileName += saveFileName + exportDate + ".xlsx";
DialogResult dialogResult = saveFileDialog1.ShowDialog();
// 如果点击了取消，则退出方法
if (dialogResult == DialogResult.Cancel)
{
return("");
}
//try
//{
// // 根据模版生成一个空白的Excel文件
// //System.IO.File.Copy(DownLoadPath, saveFileDialog1.FileName);
//}
//catch (Exception ex)
//{
// MessageBox.Show("复制Excel文件出错" + ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
//}
try
{
//SpreadsheetDocument.Create(saveFileDialog1.FileName,DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook,true);
//SpreadsheetDocument.Open(saveFileDialog1.FileName, true)
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(saveFileDialog1.FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, true))
{
#region 新建一个WorkSheet
//WorkSheet的名称
string sheetName = saveFileName;
// create the workbook
spreadSheet.AddWorkbookPart();
spreadSheet.WorkbookPart.Workbook = new Workbook(); // create the worksheet
spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>();
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
SharedStringTablePart m_SharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart <SharedStringTablePart>();
// create sheet data
SheetData m_SheetData = new SheetData();
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(m_SheetData);
// create row
//spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
//// create cell with data
//spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(new Cell() { CellValue = new CellValue("100") });
//Add Data
Row row1 = new Row()
{
RowIndex = 1
};
Cell cell1 = new Cell()
{
CellReference = "A1"
};
int index = InsertSharedStringItem("Sun", m_SharedStringTablePart);
cell1.CellValue = new CellValue(index.ToString());
cell1.DataType = new EnumValue <CellValues>(CellValues.SharedString);
row1.Append(cell1);
m_SheetData.Append(row1);
// save worksheet
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();
// create the worksheet to workbook relation
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>().AppendChild(new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = sheetName
});
spreadSheet.WorkbookPart.Workbook.Save();
#endregion
var sheetData = spreadSheet.GetFirstSheetData();
//设置单元格样式等于第一条样式
//OpenXmlHelper.CellStyleIndex = 1;
char OneColumn = Convert.ToChar('A');//第一列都是从A开始的
// 去除列表中隐藏的列
DataGridView newDataSouce = dataGridViewSource;
for (int i = dataGridViewSource.Columns.Count - 1; i >= 0; i--)
{
if (!dataGridViewSource.Columns[i].Visible)
{
//删除重复的项
newDataSouce.Columns.Remove(dataGridViewSource.Columns[i]);
}
}
//不存在模板时就补充字段名称
for (var i = 0; i < newDataSouce.Columns.Count; i++)
{
if (newDataSouce.Columns[i].Visible)
{
OneColumn = Convert.ToChar('A' + i);
//填写列的名称（暂时还是数据库中的英文）
sheetData.SetCellValue(OneColumn.ToString() + 1, newDataSouce.Columns[i].HeaderText);
}
}
if (dataGridViewSource.Rows.Count > 0)
{
for (var j = 0; j < newDataSouce.Rows.Count; j++)
{
OneColumn = Convert.ToChar('A');
for (var k = 0; k < newDataSouce.Columns.Count; k++)
{
if (!newDataSouce.Rows[j].Cells[k].Visible)
{
continue;
}
OneColumn = Convert.ToChar('A' + k);
//一个个填写值
sheetData.SetCellValue(OneColumn.ToString() + (j + 2), newDataSouce.Rows[j].Cells[k].EditedFormattedValue);
}
}
}
MessageBox.Show("导出成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("导出Excel文件出错" + ex.Message);
}
return("");
}
