C#web应用程序中的Excel跳过一些标题，不格式化
本文关键字：标题 格式化 应用程序 Excel C#web | 更新日期: 2023-09-27 18:28:01
我有一个web应用程序，它读取excel表并添加3列。在这里，我为读取和写入列的代码提供服务。
对于日期列，它不是在写它们的标题，也不是为新列写任何标题。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Activation;
using FDBService.AppCode;
using System.IO;
using System.Data.OleDb;
using System.Data;
using Microsoft.Office.Interop.Excel;
namespace FDBService
{
vFileName = string.Concat(vPath, DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.TimeOfDay.ToString().Replace(":", "").Substring(0, 6), "_", pUploadfile.fileName);
FileStream FileStream = new FileStream(vFileName, FileMode.Create);
FileStream.Write(pUploadfile.file, 0, pUploadfile.file.Length);
FileStream.Close();
FileStream.Dispose();
}
catch (Exception ex)
{
throw ex;
}
return vFileName;
}
public List<Medicine> ProcessPriceList(string pFileName)
{
List<Medicine> lstReturn = null;
string strConnection = string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", pFileName, ";Extended Properties=", "Excel 8.0;");
System.Data.DataTable dt = new System.Data.DataTable("dtSheet");
try
{
using (OleDbConnection conn = new OleDbConnection(strConnection))
{
conn.Open();
System.Data.DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
List<string> lstSheet = new List<string>();
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
{
lstSheet.Add(drSheet["TABLE_NAME"].ToString());
}
}
using (OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + lstSheet[0] + "]", conn))
{
//////conn.Open();
da.Fill(dt);
}
}
if (dt != null && dt.Rows.Count > 0)
{
dt.Columns.Add("Unit Price", typeof(string));
dt.Columns.Add("Price", typeof(string));
dt.Columns.Add("Variance", typeof(string));
dt.Columns.Add("PercentVariance", typeof(string));
string vFDBPath = System.Configuration.ConfigurationManager.AppSettings["FDBFilePath"].ToString();
FDB objFDB = null;
string[] strFDB = File.ReadAllLines(vFDBPath);
List<FDB> lstFDB = new List<FDB>();
foreach (string str in strFDB)
{
objFDB = new FDB();
objFDB.NDC = str.Substring(0, 11);
objFDB.PriceType = str.Substring(11, 2);
objFDB.Price = string.Concat(str.Substring(21, 6), ".", str.Substring(27, 5));
objFDB.Date = str.Substring(13, 8);
lstFDB.Add(objFDB);
}
double dPrice = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
string ndc = Convert.ToString(dt.Rows[i][7]);
List<FDB> objLst = (from item in lstFDB
where item.NDC == ndc && item.PriceType == "09"
orderby item.Date descending
select item).ToList();
if (objLst != null)
{
FDB objFirstItem = objLst.FirstOrDefault();
if (objFirstItem != null)
{
if (new DateTime(Convert.ToInt32(objFirstItem.Date.Substring(0, 4)), Convert.ToInt32(objFirstItem.Date.Substring(4, 2)), Convert.ToInt32(objFirstItem.Date.Substring(6, 2))) > Convert.ToDateTime(dt.Rows[i][6]))
{
if (objLst.Count > 1)
{
FDB objSecondItem = objLst[1];
dt.Rows[i]["Unit Price"] = objSecondItem.Price;
dPrice = Convert.ToDouble(objSecondItem.Price) * Convert.ToDouble(dt.Rows[i][13]);
dt.Rows[i]["Price"] = dPrice;
double billPrice = Convert.ToDouble(dt.Rows[i][12]);
dt.Rows[i]["Variance"] = (billPrice - dPrice);
if (dPrice != 0)
{
//////dt.Rows[i]["PercentVariance"] = string.Concat(Math.Round(((billPrice / dPrice) * 100), 0), " %");
dt.Rows[i]["PercentVariance"] = Math.Round(((billPrice / dPrice) * 100), 0);
}
}
}
else
{
dt.Rows[i]["Unit Price"] = objFirstItem.Price;
dPrice = Convert.ToDouble(objFirstItem.Price) * Convert.ToDouble(dt.Rows[i][13]);
dt.Rows[i]["Price"] = dPrice;
double billPrice = Convert.ToDouble(dt.Rows[i][12]);
dt.Rows[i]["Variance"] = (billPrice - dPrice);
if (dPrice != 0)
{
//////dt.Rows[i]["PercentVariance"] = string.Concat(Math.Round(((billPrice / dPrice) * 100), 0), " %");
dt.Rows[i]["PercentVariance"] = Math.Round(((billPrice / dPrice) * 100), 0);
}
}
}
}
}
catch (Exception ex)
{
//////throw ex;
}
}
lstReturn = new List<Medicine>();
Medicine objMedicine = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
try
{
FileInfo file = new FileInfo(pFileName);
if (file.Exists)
{
excelApp = new Microsoft.Office.Interop.Excel.Application();
workbook = excelApp.Workbooks.Open(pFileName, 0, false, 5, "", "",
false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
sheets = workbook.Sheets;
//check columns exist
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets)
{
Console.WriteLine(sheet.Name);
sheet.Select(Type.Missing);
/////////
lstSheetNames.Add(sheet.Name);
/////////
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = "Updated";
/////////////////////////////////////////
string strConnection = string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", pFileName, ";Extended Properties=", "Excel 8.0;");
System.Data.DataTable dt = new System.Data.DataTable("dtSheet");
try
{
using (OleDbConnection conn = new OleDbConnection(strConnection))
{
using (OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + lstSheetNames[0] + "$]", conn))
{
conn.Open();
da.Fill(dt);
}
}
}
catch (Exception ex)
{
}
dt.Columns.Add("Unit Price", typeof(string));
dt.Columns.Add("Price", typeof(string));
dt.Columns.Add("Variance", typeof(string));
dt.Columns.Add("PercentVariance", typeof(string));
FDB objFDB = null;
string[] strFDB = File.ReadAllLines(System.Configuration.ConfigurationManager.AppSettings["FDBFilePath"].ToString());
List<FDB> lstFDB = new List<FDB>();
foreach (string str in strFDB)
{
objFDB = new FDB();
objFDB.NDC = str.Substring(0, 11);
objFDB.PriceType = str.Substring(11, 2);
objFDB.Price = string.Concat(str.Substring(21, 6), ".", str.Substring(27, 5));
objFDB.Date = str.Substring(13, 8);
lstFDB.Add(objFDB);
}
double dPrice = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
string ndc = Convert.ToString(dt.Rows[i][7]);
List<FDB> objLst = (from item in lstFDB
where item.NDC == ndc && item.PriceType == "09"
orderby item.Date descending
select item).ToList();
if (objLst != null)
{
FDB objFirstItem = objLst.FirstOrDefault();
if (objFirstItem != null)
{
if (new DateTime(Convert.ToInt32(objFirstItem.Date.Substring(0, 4)), Convert.ToInt32(objFirstItem.Date.Substring(4, 2)), Convert.ToInt32(objFirstItem.Date.Substring(6, 2))) > Convert.ToDateTime(dt.Rows[i][6]))
{
if (objLst.Count > 1)
{
FDB objSecondItem = objLst[1];
dt.Rows[i]["Unit Price"] = objSecondItem.Price;
dPrice = Convert.ToDouble(objSecondItem.Price) * Convert.ToDouble(dt.Rows[i][13]);
dt.Rows[i]["Price"] = dPrice;
double billPrice = Convert.ToDouble(dt.Rows[i][12]);
dt.Rows[i]["Variance"] = (billPrice - dPrice);
if (dPrice != 0)
{
dt.Rows[i]["PercentVariance"] = string.Concat(Math.Round(((billPrice / dPrice) * 100), 0), " %");
}
}
}
else
{
dt.Rows[i]["Unit Price"] = objFirstItem.Price;
dPrice = Convert.ToDouble(objFirstItem.Price) * Convert.ToDouble(dt.Rows[i][13]);
dt.Rows[i]["Price"] = dPrice;
double billPrice = Convert.ToDouble(dt.Rows[i][12]);
dt.Rows[i]["Variance"] = (billPrice - dPrice);
if (dPrice != 0)
{
dt.Rows[i]["PercentVariance"] = string.Concat(Math.Round(((billPrice / dPrice) * 100), 0), " %");
}
}
}
}
}
catch (Exception ex)
{
//////MessageBox.Show(ex.ToString());
}
}
//////for (int i = 0; i < dt.Columns.Count; i++)
//////{
////// newSheet.Cells[1, i + 1] = dt.Columns[i].Caption;
////// newSheet.Cells[1, i + 1].Font.Bold = true;
////// //////newSheet.Cells[1, i + 1].Interior.Color = Color.FromArgb(191, 191, 191);
//////}
int rowCount = 1;
foreach (System.Data.DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
newSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
//////////////////////////////////////////
workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
////////////////////////////////
lstReturn = new List<Medicine>();
Medicine objMedicine = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
objMedicine = new Medicine();
objMedicine.FacID = dt.Rows[i][0] != DBNull.Value ? Convert.ToString(dt.Rows[i][0]) : "";
objMedicine.PatID = dt.Rows[i][1] != DBNull.Value ? Convert.ToString(dt.Rows[i][1]) : "";
objMedicine.Patient = dt.Rows[i][2] != DBNull.Value ? Convert.ToString(dt.Rows[i][2]) : "";
objMedicine.PriceCd = dt.Rows[i][3] != DBNull.Value ? Convert.ToString(dt.Rows[i][3]) : "";
objMedicine.InvoiceGrp = dt.Rows[i][4] != DBNull.Value ? Convert.ToString(dt.Rows[i][4]) : "";
objMedicine.RxNo = dt.Rows[i][5] != DBNull.Value ? Convert.ToString(dt.Rows[i][5]) : "";
objMedicine.FillDate = dt.Rows[i][6] != DBNull.Value ? Convert.ToString(dt.Rows[i][6]) : "";
objMedicine.NDC = dt.Rows[i][7] != DBNull.Value ? Convert.ToString(dt.Rows[i][7]) : "";
objMedicine.Drug = dt.Rows[i][8] != DBNull.Value ? Convert.ToString(dt.Rows[i][8]) : "";
objMedicine.BrandGen = dt.Rows[i][9] != DBNull.Value ? Convert.ToString(dt.Rows[i][9]) : "";
objMedicine.RxOTC = dt.Rows[i][10] != DBNull.Value ? Convert.ToString(dt.Rows[i][10]) : "";
objMedicine.FWBillDate = dt.Rows[i][11] != DBNull.Value ? Convert.ToString(dt.Rows[i][11]) : "";
objMedicine.FWBillAmt = dt.Rows[i][12] != DBNull.Value ? Convert.ToString(dt.Rows[i][12]) : "";
objMedicine.Quantity = dt.Rows[i][13] != DBNull.Value ? Convert.ToString(dt.Rows[i][13]) : "";
objMedicine.UnitPrice = dt.Rows[i][14] != DBNull.Value ? Convert.ToString(dt.Rows[i][14]) : "";
objMedicine.Price = dt.Rows[i][15] != DBNull.Value ? Convert.ToString(dt.Rows[i][15]) : "";
objMedicine.Variance = dt.Rows[i][16] != DBNull.Value ? Convert.ToString(dt.Rows[i][16]) : "";
objMedicine.PercentVariance = dt.Rows[i][17] != DBNull.Value ? Convert.ToString(dt.Rows[i][17]) : "";
lstReturn.Add(objMedicine);
}
catch (Exception ex)
{
}
}
}
}
MS在服务器场景（如ASP.NET、IIS、WCF、Windows服务等）中不支持互操作。
有许多选项可以在没有Interop:的情况下读取/编辑/创建Excel文件
MS提供免费的OpenXML SDK V 2.0-请参阅http://msdn.microsoft.com/en-us/library/bb448854%28office.14%29.aspx（仅限XLSX）
这可以读写MS Office文件（包括Excel）。
另一个免费选项请参阅http://www.codeproject.com/KB/office/OpenXML.aspx（仅限XLSX）
如果您需要处理较旧的Excel版本（如XLS，而不仅仅是XLSX）、渲染、创建PDF、公式等，那么有不同的免费和商业库，如ClosedXML（免费，仅限于XLSX