Excel导出

public void Export()

{

SaveFileDialog SaveFile = new SaveFileDialog();

SaveFile.FileName = "Test" + ".xlsx";

string strFilter = "xlsx文件(*.xlsx)|*.xlsx";

SaveFile.Filter = strFilter;

SaveFile.RestoreDirectory = true;

SaveFile.AddExtension = false;

if (SaveFile.ShowDialog() != DialogResult.OK)

{

return ;

}

ExportEXCEL(SaveFile.FileName);

}

private void ExportEXCEL(string strEXCLEFile)

{

if (File.Exists(strEXCLEFile))

{

File.Delete(strEXCLEFile);

}

Dictionary<string, int> dicSrcIndexToUserValue = new Dictionary<string, int>();

Dictionary<string, int> dicIndexToUserValue = new Dictionary<string, int>();

List<string> listAttrName = new List<string>();

listAttrName.Add("父项ID");

listAttrName.Add("ID");

listAttrName.Add("模型名称");

listAttrName.Add("新模型名称");

listAttrName.Add("节点类型");

listAttrName.Add("矩阵坐标");

listAttrName.Add("颜色");

listAttrName.Add("透明度");

//构造列名

DataTable tblDatas = new DataTable("UserProperty");//UserProperty 是sheet的名字

for (int nIndex = 0; nIndex < listAttrName.Count; nIndex++)

{

tblDatas.Columns.Add(listAttrName[nIndex], Type.GetType("System.String"));

}

string strFatherID = "";

string strModelID = "";

string strModelName = "";

string strNodeType = "";

string strMatrixCoordinate = "";

string strModelColor = "";

string strModelTransparency = "";

for (int i = 0; i < 10; i++)

{

List<string> listStrValue = new List<string>();

for (int n = 0; n < listAttrName.Count; n++)//一行加8个空字符

{

listStrValue.Add("");

}

listStrValue[listAttrName.FindIndex(item => item.Equals("父项ID"))] = i.ToString();

listStrValue[listAttrName.FindIndex(item => item.Equals("ID"))] = (i + 1).ToString();

listStrValue[listAttrName.FindIndex(item => item.Equals("模型名称"))] = (i + 2).ToString();

listStrValue[listAttrName.FindIndex(item => item.Equals("新模型名称"))] = "100";

listStrValue[listAttrName.FindIndex(item => item.Equals("节点类型"))] = (i + 3).ToString();

listStrValue[listAttrName.FindIndex(item => item.Equals("矩阵坐标"))] = (i + 4).ToString();

listStrValue[listAttrName.FindIndex(item => item.Equals("颜色"))] = (i + 5).ToString();

listStrValue[listAttrName.FindIndex(item => item.Equals("透明度"))] = (i + 6).ToString();

tblDatas.Rows.Add(listStrValue.ToArray()); //添加行信息

}

//保存Excel文件

TableToExcel(tblDatas, strEXCLEFile);

}

public static void TableToExcel(DataTable dt, string file)

{

IWorkbook workbook;

string fileExt = System.IO.Path.GetExtension(file).ToLower();

if (fileExt == ".xlsx")

{

workbook = new XSSFWorkbook();

}

else if (fileExt == ".xls")

{

workbook = new HSSFWorkbook(); } else { workbook = null;

}

if (workbook == null) { return; }

ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

//表头

IRow row = sheet.CreateRow(0);

for (int i = 0; i < dt.Columns.Count; i++)

{

ICell cell = row.CreateCell(i);

cell.SetCellValue(dt.Columns[i].ColumnName);

}

//数据

for (int i = 0; i < dt.Rows.Count; i++)

{

IRow row1 = sheet.CreateRow(i + 1);

for (int j = 0; j < dt.Columns.Count; j++)

{

ICell cell = row1.CreateCell(j);

cell.SetCellValue(dt.Rows[i][j].ToString());

}

}

//转为字节数组

MemoryStream stream = new MemoryStream();

workbook.Write(stream);

var buf = stream.ToArray();

//保存为Excel文件

using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))

{

fs.Write(buf, 0, buf.Length);

fs.Flush();

}

}