internal static bool loadSampleInfoFile(string fileName)
{
sampleInfoRecordList = new List <SampleInfoRecord>();
try
{
worksheet sheet = Workbook.Worksheets(fileName).First();
//sheet = ProcessWorksheet(sheet);
if (sheet.Rows.First().Cells[0].Text != "各地样本接收信息表")
{
throw new Exception("Excel表第一页应该是‘各地样本接收信息表’");
}
Console.WriteLine("num rows = " + sheet.Rows.Count());
var rows = sheet.Rows.Skip(2).Where(x => (x.Cells[3] != null && x.Cells[3].Text.Length > 0)).ToList();
foreach (Row r in rows)
{
string patientName = GetStringFromCell(r.Cells[3]);
string reportTypes = GetStringFromCell(r.Cells[9]);
string salespersonName = GetStringFromCell(r.Cells[22]);
sampleInfoRecordList.Add(new SampleInfoRecord(patientName, reportTypes, salespersonName));
}
return(true);
}
catch (Exception e)
{
userMessages = "加载样本接收信息表没成功：" + e.Message;
return(false);
}
}
internal static bool LoadSalespersonEmailList(string inputFilePath)
{
List <worksheet> excelSheets = Workbook.Worksheets(inputFilePath).ToList();
if (excelSheets.Count < 2)
{
userMessages += "Excel 文件格式不对，第一张应是销售人关系，第二张应是邮件地址\n";
return(false);
}
worksheet sheet = excelSheets[1];
salespersonEmailDict = new Dictionary <string, string>();
try
{
foreach (Row row in sheet.Rows.Skip(1).Where(x => (x.Cells[0] != null && x.Cells[1] != null && x.Cells[0].Text != "" && x.Cells[1].Text != "")))
{
if (salespersonEmailDict.ContainsKey(row.Cells[0].Text))
{
userMessages += String.Format("{0}的邮箱有重复，{1}没有加载\n", row.Cells[0].Text, row.Cells[1].Text);
}
else
{
salespersonEmailDict.Add(row.Cells[0].Text, row.Cells[1].Text);
}
userMessages += "邮箱信息加载完毕\n";
}
return(true);
}
catch (Exception e)
{
userMessages += "加载邮件地址没有成功: " + e.Message + "\n";
return(false);
}
}
private static bool loadSalespersonEmailList(worksheet sheet)
{
salespersonEmailDict = new Dictionary <string, string>();
try
{
foreach (Row row in sheet.Rows.Skip(1).Where(x => (x.Cells[0] != null && x.Cells[1] != null && x.Cells[0].Text != "" && x.Cells[1].Text != "")))
{
if (salespersonEmailDict.ContainsKey(row.Cells[0].Text))
{
userMessages += String.Format("{0}的邮箱有重复，{1}没有加载\n", row.Cells[0].Text, row.Cells[1].Text);
}
else
{
salespersonEmailDict.Add(row.Cells[0].Text, row.Cells[1].Text);
}
userMessages += "邮箱信息加载完毕\n";
}
return(true);
}
catch (Exception e)
{
userMessages += "加载邮件地址没有成功: " + e.Message + "\n";
return(false);
}
}
private void LoadEmails()
{
/* update parser with external list of emails */
String emailFile = "emails.xlsx";
worksheet ws = Workbook.Worksheets(emailFile).ElementAt(0);
parser.ParseEmails(ws);
}
public void ParseEmails(worksheet ws)
{
emailByID = new Dictionary <string, string>();
String id, email;
foreach (Row r in ws.Rows)
{
id = r.Cells[0].Value; // Value to get number without formatting
email = r.Cells[1].Text;
emailByID.Add(id, email);
}
}
/// <summary>
/// Get the header the excel file.
/// </summary>
static private List <headerCell> getSheetHeader(worksheet targetSheet)
{
List <headerCell> header = new List <headerCell>();
int i = 1;
foreach (Cell cell in targetSheet.Rows[0].Cells)
{
if (!cell.Text.ToLower().Contains("left") && !cell.Text.ToLower().Contains("right") && !cell.Text.ToLower().Contains("level"))
{
header.Add(new headerCell(cell.ColumnIndex, "h" + i++, cell.Text));
}
}
return(header);
}
private static bool loadSalesPersonHierarchyList(worksheet sheet)
{
salespersonHierarchyList = new List <SalespersonHierarchyRecord>();
try
{
//skip rows that are completely empty
foreach (Row row in sheet.Rows.Skip(1)
.Where(x => (!(x.Cells[0] == null && x.Cells[1] == null && x.Cells[2] == null && x.Cells[3] == null && x.Cells[4] == null &&
string.IsNullOrEmpty(x.Cells[0].Text) && string.IsNullOrEmpty(x.Cells[1].Text) && string.IsNullOrEmpty(x.Cells[2].Text) &&
string.IsNullOrEmpty(x.Cells[3].Text) && string.IsNullOrEmpty(x.Cells[4].Text)))))
{
salespersonHierarchyList.Add(new SalespersonHierarchyRecord(row.Cells[0], row.Cells[1], row.Cells[2], row.Cells[3], row.Cells[4]));
}
return(true);
}
catch (Exception e)
{
userMessages += "加载发送关系没有成功: " + e.Message + "\n";
return(false);
}
}
// parse data from a given exported qualification sheet
public void ParseSheet(String fileSource)
{
if (fileSource != null)
{
if (System.IO.File.Exists(saveFileSrc))
{
newReportSrc = fileSource;
}
else
{
// file not found
return;
}
}
else
{
// no file given
return;
}
worksheet ws = Workbook.Worksheets(fileSource).ElementAt(0);
employees = new Dictionary <String, Employee>();
expiring = new List <Employee>();
Excel.Cell[] row;
String id;
Employee emp;
int[] dmy;
foreach (Row r in ws.Rows)
{
if (r == null)
{
break;
}
row = r.Cells;
if (row[4] == null)
{
continue; // bottom (usually) line is half empty
}
id = row[0].Text;
if (id == "ID")
{
continue; // header row
}
// get existing employee if exists
if (employees.ContainsKey(id))
{
emp = employees[id];
}
// othewise create a new one
else
{
emp = new Employee()
{
ID = id,
LastName = row[1].Text,
GivenName = row[2].Text
};
// add to dictionary
employees.Add(id, emp);
}
// parse the certification
dmy = ExcelSerialDateToDMY(int.Parse(row[6].Value));
Certification cert = new Certification()
{
Name = row[3].Text,
Expiry = new DateTime(dmy[2], dmy[1], dmy[0])
};
cert.DaysLeft = (int)(cert.Expiry - DateTime.Now).TotalDays;
// append certification to employee
emp.Certifications[cert.Name] = cert;
// mark expiring qual
if (cert.DaysLeft <= 60)
{
emp.Expiring = true;
}
}
ParseSaved();
}
public Excel(string path, int sheet)
{
this.path = path;
wb = excel.Workbooks.Open(path);
ws = wb.Worksheets[sheet]; // Not sure if this is a square or curly bracket.
}
/// <summary>
/// Build Sheet XML string
/// </summary>
/// <param name="excelsheet"></param>
/// <param name="xmldocument"></param>
/// <returns></returns>
public string BuildSheetXML(Sheet excelsheet, ref XmlDocument xmldocument, int sharedstringcount = 0)
{
try
{
var cells = excelsheet.Cells;
var rowstyles = excelsheet.RowStyles;
var columnstyles = excelsheet.ColumnStyles;
var startRowIndex = cells.Min(x => x.Address.Row);
var endRowIndex = cells.Max(x => x.Address.Row);
var startColumnIndex = cells.Min(x => x.Address.Column.Index);
var endColumnIndex = cells.Max(x => x.Address.Column.Index);
var noofRows = endRowIndex - startRowIndex + 1;
var noofCols = endColumnIndex - startRowIndex + 1;
//sorting by cell index
cells = cells.OrderBy(x => x.Address.Row).ThenBy(x => x.Address.Column.Index).ToList();
worksheet worksheet = new worksheet();
worksheet.sheetviews = new sheetViews();
worksheet.sheetFormatPr = new sheetFormatPr()
{
defaultRowHeight = "15", dyDescent = ".25"
};
worksheet.sheetData = new sheetData();
worksheet.sheetData.row = new List <row>();
//add column Styles
if (columnstyles.Count > 0)
{
worksheet.cols = new cols();
worksheet.cols.col = new List <col>();
foreach (var columnstyle in columnstyles)
{
var col = new col();
col.customWidth = "1";
col.max = col.min = columnstyle.Index.ToString();
col.width = columnstyle.Width.ToString();
worksheet.cols.col.Add(col);
}
}
List <int> addedStyleIds = new List <int>();
int fontId = 0; int fillId = 1; int borderId = 0; // initializing fontid,fillid and borderid
int styles = 0;
//Gets the Existing XML Nodes/Elements from Style Sheet
var fonts = xmldocument.GetElementsByTagName("fonts").FirstOrDefault();
var fills = xmldocument.GetElementsByTagName("fills").FirstOrDefault();
var borders = xmldocument.GetElementsByTagName("borders").FirstOrDefault();
var cellXfs = xmldocument.GetElementsByTagName("cellXfs").FirstOrDefault();
var count = cells.Where(x => null != x.MergeCell).Count();
if (count > 0)
{
worksheet.mergeCells = new mergeCells()
{
count = count.ToString()
};
worksheet.mergeCells.mergeCell = new List <mergeCell>();
}
for (var rowindx = startRowIndex; rowindx <= endRowIndex; rowindx++)
{
row row = new row();
row.r = rowindx.ToString();
row.spans = startColumnIndex.ToString() + ":" + endColumnIndex.ToString();
row.dyDescent = "0.25";
row.c = new List <c>();
//var rstyle = rowstyles.Where(x => x.Index == rowindx).FirstOrDefault();
//if (null != rstyle)
//{
// row.height = rstyle.Height.ToString();
// row.customHeight = "1";
//}
for (var colindx = startColumnIndex; colindx <= endColumnIndex; colindx++)
{
int cellindx = ((rowindx - 1) * columnstyles.Count) + (colindx - 1);
var currentcell = cells[cellindx];//.FirstOrDefault(x => x.Address.Row == rowindx && x.Address.Column.Index == colindx);
if (null != currentcell)
{
var index = cellindx + sharedstringcount;// cells.IndexOf(currentcell) + sharedstringcount; //cells.IndexOf(currentcell)
var c = currentcell.ToXml(index);
if (null != currentcell.Style)
{
var isAdded = addedStyleIds.Any(x => x == currentcell.Style.StyleId);
//Add styles to stylesheet if not added already
if (!isAdded)
{
addedStyleIds.Add(currentcell.Style.StyleId);
var fntElem = currentcell.Style.ToFontElement(xmldocument);
if (null != fntElem)
{
fonts.AppendChild(fntElem);
fontId++;
}
var fillElem = currentcell.Style.ToFillElement(xmldocument);
if (null != fillElem)
{
fills.AppendChild(fillElem);
fillId++;
}
var borderElem = currentcell.Style.ToBorderElement(xmldocument);
if (null != borderElem)
{
borders.AppendChild(borderElem);
borderId++;
}
var xfElem = currentcell.Style.ToXFElement(xmldocument, fontId, fillId, borderId);
if (null != xfElem)
{
cellXfs.AppendChild(xfElem);
styles++;
}
}
c.s = currentcell.Style.StyleId.ToString();
}
if (null != currentcell.MergeCell)
{
var startaddress = currentcell.ToAddress();
var endaddress = new Cell()
{
Address = currentcell.MergeCell
}.ToAddress();
worksheet.mergeCells.mergeCell.Add(new mergeCell {
reference = startaddress + ":" + endaddress
});
}
row.c.Add(c);
}
}
fonts.Attributes[0].InnerText = (fontId + 1).ToString();
cellXfs.Attributes[0].InnerText = (styles + 1).ToString();
fills.Attributes[0].InnerText = (fillId + 1).ToString();
borders.Attributes[0].InnerText = (borderId + 1).ToString();
if (null != row && row.c.Count > 0)
{
worksheet.sheetData.row.Add(row);
}
}
worksheet.pageMargins = new pageMargins();
worksheet.dimension = new dimension();
worksheet.dimension.reference = cells[0].ToAddress() + ":" + cells[cells.Count - 1].ToAddress();
var dixt = new Dictionary <string, string>();
dixt.Add("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
//dixt.Add("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
//dixt.Add("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
string ss = Serialize(worksheet, dixt);
ss = ss.Replace("encoding=\"utf-16\"", "encoding=\"UTF-8\" standalone=\"yes\"");
return(ss);
}
catch
{
throw;
}
}
/// <summary>
/// Creates an XML file from a SalesForce export.
/// </summary>
static private bool writeTheXml(worksheet targetSheet, string outFile, int levelCol)
{
bool runResult = false;
int prevLevel = -1, levelsDiff;
int currLevel = 0;
var i = 1;
List <headerCell> excelHeader;
excelHeader = getSheetHeader(targetSheet);
XmlWriterSettings writerSettings = new XmlWriterSettings();
writerSettings.OmitXmlDeclaration = true;
writerSettings.Indent = true;
writerSettings.IndentChars = " ";
writerSettings.ConformanceLevel = ConformanceLevel.Document;
writerSettings.CloseOutput = true;
//This is the meat and potatoes
try
{
using (XmlWriter writer = XmlWriter.Create(outFile, writerSettings))
{
writer.WriteStartDocument(); //This isn't required with the current XmlWriterSettings
writer.WriteStartElement("table");
writer.WriteStartElement("header");
foreach (headerCell cell in excelHeader)
{
writer.WriteElementString(cell.uid, cell.text);
}
writer.WriteEndElement();
foreach (Row row in targetSheet.Rows.Skip(1)) //PRIMARY LOOP //Skip the first row (header)
{
currLevel = Convert.ToInt32(row.Cells[levelCol].Text);
//FIRST: Open a "nest" OR Close previous categories (and nests)
if (i == 1)
{
; //Do not nest (or close elements) on the first row.
}
else if (currLevel > prevLevel)
{
writer.WriteStartElement("nest");
}
else
{
for (levelsDiff = prevLevel - currLevel; levelsDiff >= 0; levelsDiff--)
{
writer.WriteEndElement();//close cat
if (levelsDiff > 0)
{
writer.WriteEndElement(); //close nest
}
}
}
//SECOND: Open and populate current category
writer.WriteStartElement("cat");
writer.WriteStartElement("values");
foreach (headerCell cell in excelHeader)
{
writer.WriteElementString(cell.uid, row.Cells[cell.col].Text);
}
writer.WriteEndElement();
prevLevel = currLevel;
i++;
}// loop to next row
//cleanup
writer.WriteEndDocument(); //closes hierarchy and table. Safer than explicitly closing each.
writer.Flush(); //clear the buffer
writer.Close(); //free the allocation, close the file stream (Not required while in a "using" block)
runResult = true;
}
}
catch (Exception) {}
return(runResult);
}
