基于WebApi接口
调用：
try
{
MES.BLL.extend.TEST_PROJECT_EXTEND MaterialBLL = new MES.BLL.extend.TEST_PROJECT_EXTEND();
result = MaterialBLL.QueryAllocationproduct(identificationproduct);
string[] Cellnames = new string[] { "产品名称", "产品型号", "类型", "版本号", "生产资料名称", "上传时间" };
AllocationList = MaterialBLL.DataExcelToList(result.Tables[0]);
DataTable dt = ExcelHelper.ListToDataTable(AllocationList);
ExcelHelper.ExportExcel(dt, Cellnames);
}
ListToDataTable：
public static DataTable ListToDataTable<T>(List<T> entitys)
{
//检查实体集合不能为空
if (entitys == null || entitys.Count < 1)
{
return new DataTable();
}
//取出第一个实体的所有Propertie
Type entityType = entitys[0].GetType();
PropertyInfo[] entityProperties = entityType.GetProperties();
//生成DataTable的structure
//生产代码中，应将生成的DataTable结构Cache起来，此处略
DataTable dt = new DataTable("dt");
for (int i = 0; i < entityProperties.Length; i++)
{
//dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
dt.Columns.Add(entityProperties[i].Name);
}
//将所有entity添加到DataTable中
foreach (object entity in entitys)
{
//检查所有的的实体都为同一类型
if (entity.GetType() != entityType)
{
throw new Exception("要转换的集合元素类型不一致");
}
object[] entityValues = new object[entityProperties.Length];
for (int i = 0; i < entityProperties.Length; i++)
{
entityValues[i] = entityProperties[i].GetValue(entity, null);
}
dt.Rows.Add(entityValues);
}
return dt;
}
方法体：
//导出
public static void ExportExcel(DataTable dt, string[] Cellnames)
{
if (dt != null)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
return;
}
//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excel.Visible = false;
//打开时设置为全屏显式
//excel.DisplayFullScreen = true;
//初始化工作簿
Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
//新增加一个工作簿，Add（）方法也可以直接传入参数 true
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//同样是新增一个工作簿，但是会弹出保存对话框
//Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
//新增加一个 Excel 表(sheet)
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//设置表的名称
worksheet.Name = dt.TableName;
try
{
//创建一个单元格
Microsoft.Office.Interop.Excel.Range range;
int rowIndex = 1; //行的起始下标为 1
int colIndex = 1; //列的起始下标为 1
// worksheet.Cells[1, 1] = "列名1";
// worksheet.Cells[1, 2] = "列名2";
for (int i = 0; i < Cellnames.Length; i++)
{
worksheet.Cells[rowIndex, colIndex + i] = Cellnames[i];
}
//设置列名
for (int i = 0; i < dt.Columns.Count; i++)
{
//设置第一行，即列名
//worksheet.Cells[rowIndex, colIndex + i] = dt.Columns[i].ColumnName;
//获取第一行的每个单元格
range = worksheet.Cells[rowIndex, colIndex + i];
//设置单元格的内部颜色
range.Interior.ColorIndex = 33;
//字体加粗
range.Font.Bold = true;
//设置为黑色
range.Font.Color = 0;
//设置为宋体
range.Font.Name = "Arial";
//设置字体大小
range.Font.Size = 12;
//水平居中
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//垂直居中
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
//跳过第一行，第一行写入了列名
rowIndex++;
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[rowIndex + i, colIndex + j] = dt.Rows[i][j].ToString();
}
}
//设置所有列宽为自动列宽
//worksheet.Columns.AutoFit();
//设置所有单元格列宽为自动列宽
worksheet.Cells.Columns.AutoFit();
//worksheet.Cells.EntireColumn.AutoFit();
//是否提示，如果想删除某个sheet页，首先要将此项设为fasle。
excel.DisplayAlerts = false;
//保存写入的数据，这里还没有保存到磁盘
workbook.Saved = true;
//设置导出文件路径
//string path = HttpContext.Current.Server.MapPath("Export/");
string path = "D:\\debugMES\\API\\WebAPIMes\\Export\\";
//设置新建文件路径及名称
string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
//创建文件
FileStream file = new FileStream(savePath, FileMode.CreateNew);
//关闭释放流，不然没办法写入数据
file.Close();
file.Dispose();
//保存到指定的路径
workbook.SaveCopyAs(savePath);
//还可以加入以下方法输出到浏览器下载
FileInfo fileInfo = new FileInfo(savePath);
OutputClient(fileInfo);
}
catch (Exception ex)
{
}
finally
{
workbook.Close(false, Type.Missing, Type.Missing);
workbooks.Close();
//关闭退出
excel.Quit();
//释放 COM 对象
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);
worksheet = null;
workbook = null;
workbooks = null;
excel = null;
GC.Collect();
}
}
}
网页下载：
public static void OutputClient(FileInfo file)
{
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
//导出到 .xlsx 格式不能用时，可以试试这个
//HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd-HH-mm")));
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
HttpContext.Current.Response.WriteFile(file.FullName);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.Close();
}
导入（Winform验证）：
调用如果为：true不加表头Cellnames
private void btn_imp_Click(object sender, EventArgs e)
{
string[] Cellnames = new string[] { "产品名称", "产品型号", "类型", "版本号", "生产资料名称", "上传时间" };
GetDataFromExcelByCom(Cellnames, true);
}
方法体：
System.Data.DataTable GetDataFromExcelByCom(string[] Cellnames, bool hasTitle = false )
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
openFile.Multiselect = false;
if (openFile.ShowDialog() == DialogResult.Cancel) return null;
var excelFilePath = openFile.FileName;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Sheets sheets;
object oMissiong = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
System.Data.DataTable dt = new System.Data.DataTable();
try
{
if (app == null) return null;
workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
sheets = workbook.Worksheets;
//将数据读入到DataTable中
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);//读取第一张表
if (worksheet == null) return null;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
//生成列头
for (int i = 0; i < iColCount; i++)
{
//var name = "column" + i;
var name = Cellnames[i];
if (hasTitle)
{
var txt = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
if (!string.IsNullOrWhiteSpace(txt)) name = txt;
}
while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行数据
Microsoft.Office.Interop.Excel.Range range;
int rowIdx = hasTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
}
dt.Rows.Add(dr);
}
dt1 = dt;
dataGridView1.DataSource = dt;
return dt;
}
catch { return null; }
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
}
标签：Office,Interop,Excel,导出,导入,worksheet,dt,Microsoft From： https://www.cnblogs.com/shiningleo007/p/16935314.html