public static void CreateResultsWorksheet(DataSet Schedule)
{
// Frequenty-used variable for optional arguments.
object m_objOpt = System.Reflection.Missing.Value;
Application xlApp = new Application();
Worksheet ws = new Worksheet();
int outRows, outCols;
//Rows and columns are transposed for printing
outCols = Schedule.Tables[0].Columns.Count;
outRows = Schedule.Tables[0].Rows.Count + 1; //leave roof for row and column headers
object[,] OutputArray = new object[outRows, outCols];
////Note Excel Arrays are 1-based, not 0-based
for (int i = 0; i < outCols; i++)
{
//Put the titles into the spreadsheet.
OutputArray[0, i] = Schedule.Tables[0].Columns[i].ColumnName;
}
for (int i = 1; i < outRows; i++)
{
for (int j = 0;j < outCols; j++)
{
DataRow dr = Schedule.Tables[0].Rows[i - 1];
OutputArray[i, j] = dr[j];
}
}
//Create a workbook and add a worksheet named "Schedule Results"
xlApp.Workbooks.Add(m_objOpt);
ws = (Worksheet) xlApp.Workbooks[1].Worksheets[1];
ws.Name = "Schedule Results";
Range r = ws.get_Range(ws.Cells[1,1],ws.Cells[outRows, outCols]);
//put the output array into Excel in one step.
r.Value2=OutputArray;
//select the title columns and make them bold
r = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, outCols + 1]);
r.Font.Bold = true;
r.Font.Size = 14;
//format any DateTime Columns
for (int i = 0; i<outCols; i++)
{
if (Schedule.Tables[0].Columns[i].DataType.ToString() == "System.DateTime" )
{
r = ws.get_Range(ws.Cells[1, i+1], ws.Cells[outRows + 1, i+1]);
r.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@";
}
}
//Select the entire spreadsheet and autofit the contents
r = ws.get_Range(ws.Cells[1, 1], ws.Cells[outRows + 1, outCols + 1]);
r.Columns.AutoFit();
xlApp.Visible = true;
xlApp = null;
}
private static void AddComment(Worksheet worksheet, string note, byte columnIndex)
{
int commentIndex = worksheet.Comments.Add(0, columnIndex);
Aspose.Cells.Comment comment = worksheet.Comments[commentIndex];
comment.Note = note;
comment.WidthCM = 20;
}
private void Delete(int? sheetNumber = null, string sheetName = null)
{
CheckFiles();
PrepareArchive(false);
// Get worksheet details
Worksheet worksheet = new Worksheet();
worksheet.GetWorksheetProperties(this, sheetNumber, sheetName);
// Delete the file
if (!string.IsNullOrEmpty(worksheet.FileName))
{
ZipArchiveEntry entry = this.Archive.GetEntry(worksheet.FileName);
if (entry != null)
{
entry.Delete();
}
if (this.DeleteWorksheets == null)
{
this.DeleteWorksheets = new List<int>();
}
this.DeleteWorksheets.Add(worksheet.Index);
}
}
public void Parse()
{
currentSheet = xlWorkBook.Worksheets.get_Item(1);
Range range = currentSheet.UsedRange;
string page = "";
for (int i = 1; i < range.Rows.Count+1; i++)
{
using (WebClient clien = new WebClient())
{
try
{
page = clien.DownloadString((string)(currentSheet.Cells[i, 1] as Range).Value);
}
catch (Exception) { }
if (page.Contains(linkurl))
{
currentSheet.Cells[i, 2] = "Ссылка есть";
}
else
{
currentSheet.Cells[i, 2] = "Ссылки нет";
}
}
}
xlWorkBook.Save();
Dispose();
}
public static void EnsureColumn(Worksheet worksheet, uint columnIndex)
{
var columns = worksheet.Elements<Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new Columns(), 0);
}
if (columns.Elements<Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
Column previousColumn = null;
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements<Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(new Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
}
public IEnumerable<ExpandoObject> GetObjects(Worksheet worksheet, ISheetDefinition sheetDefinition)
{
if (worksheet == null) throw new ArgumentNullException("worksheet");
if (sheetDefinition == null) throw new ArgumentNullException("sheetDefinition");
var rows = worksheet.Descendants<Row>().Skip(1);
return rows.Select(x => ParseFromRow(x, sheetDefinition));
}
// 打开Excel文件
public void OpenExcelFile()
{
OpenFileDialog opd = new OpenFileDialog();
if (opd.ShowDialog() == DialogResult.OK)
excelOpenFileName = opd.FileName;
textBox4.Text = System.IO.Path.GetFileNameWithoutExtension(excelOpenFileName);
if (excelOpenFileName !="")
{
try
{
excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelWorkBooks = excelApplication.Workbooks;
excelWorkBook = ((Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
excelWorkSheet = (Worksheet)excelWorkBook.Worksheets[ActiveSheetIndex];
excelApplication.Visible = false;
rangeOfExcel = excelWorkSheet.UsedRange.Cells.Rows.Count;//获取EXCEL行数
stateOfSave = false;
}
catch (Exception e)
{
closeApplication();
MessageBox.Show("(1)没有安装Excel；(2)或没有安装.NET 可编程性支持；\n详细信息："
+ e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("未选择文件！");
closeApplication();
}
}
//
//**********************************************************************************************
//
public TAssignmentMethod ReadAssignmentMethod()
{
TAssignmentMethod MyMethod = TAssignmentMethod.None;
MyWorkSheet = (Worksheet)MyWorkBook.Worksheets.get_Item("Control");
Range MyRange;
try
{
MyRange = (Range)MyWorkSheet.Cells[19, 3];
Int32 M = Convert.ToInt32(MyRange.Value2);
switch (M)
{
case 0: MyMethod = TAssignmentMethod.Interpolate;
break;
case 1: MyMethod = TAssignmentMethod.NearestNeighbour;
break;
default: MyMethod = TAssignmentMethod.None;
break;
}
}
catch (Exception e)
{
MessageBox.Show("Error reading method: " + e.Message);
}
return MyMethod;
}
private static void fillSection(SvodDdr.DdrDataContext dc, Worksheet ws, List<SourceData> sqlData, List<SourceData> usedData, int minRow, int maxRow)
{
for (int rowIndex = minRow; rowIndex <= maxRow; rowIndex++)
{
fillRow(dc, rowIndex, ws, sqlData, usedData);
}
}
public void Test()
{
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Test");
string styleId = workbook.Styles.Add(new Style
{
Alignment = new Alignment
{
Horizontal = "Center"
}
});
for (int i = 1; i < 10; i++)
{
for (int j = 1; j <= i; j++)
{
string str = string.Format("{0} X {1} = {2}", j, i, i*j);
worksheet.Tables.Columns[i].Width = 65;
worksheet.Tables.Rows[i].Cells[j] = new Cell(str)
{
StyleId = styleId
};
}
}
workbook.WorkSheets.Add(worksheet);
_excel.Workbooks.Add(workbook);
workbook.Save("c:\\aa.xml");
}
public ExcelReader(TableInfo structure, Worksheet worksheet)
: base(structure)
{
_worksheet = worksheet;
_array = new string[structure.ColumnCount];
_usedRange = _worksheet.UsedRange;
}
public SheetViews(Worksheet worksheet, bool selected, int frozenRow, int frozenColumn)
: this(worksheet)
{
Selected = selected;
FrozenRow = frozenRow;
FrozenColumn = frozenColumn;
}
private static Worksheet mySheet1; //工作簿1
#endregion Fields
#region Methods
public static List<List<string>> Read(string fileName,int columnCount,int startRowIndex)
{
List<List<string>> textList = new List<List<string>>();
excelApp = new ApplicationClass();
Workbooks myWorkBooks = excelApp.Workbooks;
myWorkBooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Sheets sheets = excelApp.Sheets;
mySheet1 = (Worksheet)sheets[1];
int rowCount = mySheet1.UsedRange.Rows.Count;
if (rowCount != 0)
{
for (int i = startRowIndex; i <= rowCount; i++)
{
string name = ((Range)mySheet1.Cells[i, 2]).Text.ToString();
if (name != "") {
List<string> list = new List<string>();
list.Add((i-startRowIndex+1).ToString());
for (int j = 0; j < columnCount; j++)
{
list.Add(((Range)mySheet1.Cells[i, j + 1]).Text.ToString());
}
textList.Add(list);
}
}
}
myWorkBooks.Close();
excelApp.Quit();
excelApp = null;
return textList;
}
public static void SaveExcelFile(string fileName, string tempFileName,string[,] contents, int startColumn, int startRow)
{
try
{
excelApp = new ApplicationClass();
Workbooks myWorkBooks = excelApp.Workbooks;
myWorkBooks.Open(tempFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Sheets sheets = excelApp.Sheets;
mySheet1 = (Worksheet)sheets[1];
mySheet1.Activate();
//写入测试信息
Range range1 = mySheet1.get_Range(mySheet1.Cells[startRow, startColumn], mySheet1.Cells[contents.GetLength(0)+startRow-1,contents.GetLength(1)+startColumn-1]);
range1.Value2 = contents;
mySheet1.SaveAs(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
myWorkBooks.Close();
excelApp.Quit();
excelApp = null;
}
catch (Exception ee)
{
throw ee;
}
}
private void fillExcle(Worksheet sheet, List<Config> list, SqlCommand sqlComm)
{
for (int i = 0; i < list.Count; i++)
{
SqlDataReader reader = null;
Config con = list[i];
try
{
sqlComm.CommandText = con.Sql;
reader = sqlComm.ExecuteReader();
if (reader.Read())
{
object data = reader.GetValue(0);
sheet.Cells[con.Pos].PutValue(data);
}
reader.Close();
}
catch (Exception ee)
{
sheet.Cells[con.Pos].PutValue(ee.Message);
if (reader != null)
reader.Close();
}
}
}
private Worksheet Read(int? sheetNumber = null, string sheetName = null, int existingHeadingRows = 0)
{
CheckFiles();
PrepareArchive();
Worksheet worksheet = new Worksheet();
worksheet.ExistingHeadingRows = existingHeadingRows;
worksheet.GetWorksheetProperties(this, sheetNumber, sheetName);
IEnumerable<Row> rows = null;
List<string> headings = new List<string>();
using (Stream stream = this.Archive.GetEntry(worksheet.FileName).Open())
{
XDocument document = XDocument.Load(stream);
int skipRows = 0;
Row possibleHeadingRow = new Row(document.Descendants().Where(d => d.Name.LocalName == "row").FirstOrDefault(), this.SharedStrings);
if (worksheet.ExistingHeadingRows == 1 && possibleHeadingRow.RowNumber == 1)
{
foreach (Cell headerCell in possibleHeadingRow.Cells)
{
headings.Add(headerCell.Value.ToString());
}
}
rows = GetRows(document.Descendants().Where(d => d.Name.LocalName == "row").Skip(skipRows));
}
worksheet.Headings = headings;
worksheet.Rows = rows;
return worksheet;
}
public string GetCellStringValue(Worksheet sheet, string cellName)
{
var cell = sheet.Cells[cellName];
if (cell.Value == null)
return string.Empty;
return cell.Value.ToString();
}
public object GetCellValue(Worksheet sheet, SheetDataColumn column, string cellName)
{
var cell = sheet.Cells[cellName];
if (cell.Value == null)
{
return DBNull.Value;
}
else
{
switch (column.DataType)
{
case DataType.DateTime:
DateTime dateTimeValue;
if (DateTime.TryParse(cell.Value.ToString(), out dateTimeValue))
return dateTimeValue;
else
return DBNull.Value;
case DataType.Integer:
Int32 intValue;
if (Int32.TryParse(cell.Value.ToString(), out intValue))
return intValue;
else
return DBNull.Value;
case DataType.Double:
Double doubleValue;
if (Double.TryParse(cell.Value.ToString(), out doubleValue))
return doubleValue;
else
return DBNull.Value;
case DataType.String:
default:
return (string)cell.Value;
}
}
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static HeaderFooter ReadHeaderFooterFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
HeaderFooter headerFooter = new HeaderFooter(worksheet);
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "alignWithMargins":
headerFooter.AlignWithMargins = attribute.GetBoolValue();
break;
default:
throw new Exception(string.Format("HeaderFooter attribute {0} not coded", attribute.LocalName));
}
}
while (reader.ReadToEndElement<OpenXmlSpreadsheet.HeaderFooter>())
{
if (reader.IsStartElementOfType<OpenXmlSpreadsheet.OddHeader>())
headerFooter.OddHeader = reader.GetText();
else if (reader.IsStartElementOfType<OpenXmlSpreadsheet.OddFooter>())
headerFooter.OddFooter = reader.GetText();
}
return headerFooter;
}
private Worksheet[] GetWorksheetProperties()
{
CheckFiles();
PrepareArchive(false);
var worksheets = new List<Worksheet>();
using (Stream stream = this.Archive.GetEntry("xl/workbook.xml").Open())
{
XDocument document = XDocument.Load(stream);
if (document == null)
{
throw new Exception("Unable to load workbook.xml");
}
List<XElement> sheetsElements = document.Descendants().Where(d => d.Name.LocalName == "sheet").ToList();
foreach (var sheetElement in sheetsElements)
{
var worksheet = new Worksheet(this);
worksheet.Index = sheetsElements.IndexOf(sheetElement) + 1;
worksheet.Name = (from attribute in sheetElement.Attributes()
where attribute.Name == "name"
select attribute.Value).FirstOrDefault();
worksheets.Add(worksheet);
}
}
return worksheets.ToArray();
}
private void setDTOInfo(Worksheet sheet, List<HcDTOInfo> dtoList)
{
var name = sheet.Name;
if (name.Length > 3 && name.Substring(name.Length - 3, 3).Equals("DTO"))
{
var dtoInfo = new HcDTOInfo();
Range range = null;
range = (Range)sheet.Cells[2, 3];
dtoInfo.Name = range.Value.ToString();
range = (Range)sheet.Cells[1, 3];
dtoInfo.Caption = range.Value.ToString();
int iRow = 5;
range = (Range)sheet.Cells[iRow, 3];
var cellValue = range.Value;
dtoInfo.FieldArray = new List<HcFieldInfo>();
while (cellValue != null && !string.IsNullOrEmpty(cellValue.ToString()))
{
var field = new HcFieldInfo();
field.name = cellValue.ToString();
range = (Range)sheet.Cells[iRow, 2];
field.caption = range.Value.ToString();
range = (Range)sheet.Cells[iRow, 4];
field.FieldTypeString = range.Value.ToString();
dtoInfo.FieldArray.Add(field);
iRow += 1;
range = (Range)sheet.Cells[iRow, 3];
cellValue = range.Value;
}
dtoList.Add(dtoInfo);
}
}
public HeaderFooter(Worksheet worksheet, string oddHeader, string oddFooter, bool alignHeaderFooterWithMargins)
: this(worksheet)
{
OddHeader = oddHeader;
OddFooter = oddFooter;
AlignWithMargins = alignHeaderFooterWithMargins;
}
public bool CheckValidTemplate(string username, string language_id, string store_procedure, string file_name, string module_id, string function_id, Worksheet ws)
{
bool result = false;
DataRow dr = db.GetDataRow("SYS_spfrmImportFileConfig", new string[] { "Activity", "Username", "LanguageID", "ExcelFile", "FunctionID", "ModuleID" }, new object[] { "CheckValidTemplate", username, language_id, file_name, function_id, module_id });
if (dr != null)
result = true;
else
{
DataTable dt = db.GetDataTable("SYS_spCommon", new string[] { "ObjectName" }, new object[] { store_procedure });
if (dt != null && dt.Rows.Count > 0)
{
int count = 0;
for (int i = 0; i < ws.Cells.MaxColumn; i++)
{
if (string.IsNullOrEmpty(ws.Cells[1, i].Value + ""))
break;
string tmp = ws.Cells[1, i].Value.ToString().ToLower().Trim();
if (string.IsNullOrEmpty(tmp))
break;
if (tmp != "stt" && tmp != "$hidecolumn$" && tmp != "$deletecolumn$")
{
DataRow[] rows = dt.Select("ColumnName='@" + tmp + "'");
if (rows.Length == 0) count++;
}
}
result = (count < 3);
}
else result = false;
}
return result;
}
internal ExcelWorksheet(Worksheet worksheet, ExcelDocument parent)
{
IsDisposed = false;
Worksheet = worksheet;
Parent = parent;
}
/// <summary>
/// 取得欄位驗證字串
/// </summary>
/// <returns></returns>
public static string GetChekcDataStr(int idx, Worksheet wst, Dictionary<string, int> ColIndexDic)
{
string chkStr = string.Empty;
if (ColIndexDic.ContainsKey("學號"))
chkStr += wst.Cells[idx, ColIndexDic["學號"]].StringValue;
if (ColIndexDic.ContainsKey("姓名"))
chkStr += wst.Cells[idx, ColIndexDic["姓名"]].StringValue;
//if (ColIndexDic.ContainsKey("學年度"))
// chkStr += wst.Cells[idx, ColIndexDic["學年度"]].StringValue;
//if (ColIndexDic.ContainsKey("學期"))
// chkStr += wst.Cells[idx, ColIndexDic["學期"]].StringValue;
if (ColIndexDic.ContainsKey("異動日期"))
chkStr += wst.Cells[idx, ColIndexDic["異動日期"]].StringValue;
if (ColIndexDic.ContainsKey("異動代碼"))
chkStr += wst.Cells[idx, ColIndexDic["異動代碼"]].StringValue;
if (ColIndexDic.ContainsKey("原因及事項"))
chkStr += wst.Cells[idx, ColIndexDic["原因及事項"]].StringValue;
return chkStr;
}
//Open Excel file
public int FnOpenExcel(string sPath, string iSheet)
{
int functionReturnValue = 0;
try
{
application = new Microsoft.Office.Interop.Excel.Application { Visible = true }; //Microsoft.Office.Interop.Excel.Application();
workbook = application.Workbooks.Open(sPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// get all sheets in workbook
//worksheet = (Worksheet) workbook.Worksheets;
// get some sheet
//string currentSheet = "Main Invoice";
worksheet = (Worksheet) workbook.Worksheets["" + iSheet + ""];
// Get the active sheet
worksheet = (Worksheet)workbook.ActiveSheet;
functionReturnValue = 0;
}
catch (Exception ex)
{
functionReturnValue = -1;
MessageBox.Show(ex.Message);
}
return functionReturnValue;
}
public Export(bool defaultBackgroundIsWhite)
{
app = new Application();
app.Visible = true;
workbook = app.Workbooks.Add(1);
worksheet = (Worksheet)workbook.Sheets[1];
}
/// <summary>
/// 添加Excel头部
/// </summary>
/// <param name="dt"></param>
private void AddHeader(DataTable dt, Worksheet sheetNew = null, Hashtable ht = null)
{
if (sheetNew == null)
{
sheetNew = sheet;
}
Cell cell = null;
for (int col = 0; col < dt.Columns.Count; col++)
{
cell = sheetNew.Cells[0, col];
if (ht == null)
{
cell.PutValue(dt.Columns[col].ColumnName);
}
else
{
string name = ht[dt.Columns[col].ColumnName].ToString() ?? dt.Columns[col].ColumnName;
cell.PutValue(name);
}
Style style = new Style();
style.Font.IsBold = true;
cell.SetStyle(style);
}
}
public DataGridViewExport(DataGridView dgv)
{
_workbook = new Workbook();
_workbook.Worksheets.Clear();
_worksheet = _workbook.Worksheets[_workbook.Worksheets.Add()];
_worksheet.Name = "Sheet1";
_colIndexes = new List<int>();
int sheetRowIndex = 0;
int sheetColIndex = 0;
foreach (DataGridViewColumn col in dgv.Columns)
{
if (col.Visible == false) continue;
_colIndexes.Add(col.Index);
_worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue(col.HeaderText);
}
foreach (DataGridViewRow row in dgv.Rows)
{
sheetRowIndex++;
sheetColIndex = 0;
foreach (int colIndex in _colIndexes)
_worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue("" + row.Cells[colIndex].Value);
}
_worksheet.AutoFitColumns();
}
private void setInDTO(Worksheet sheet, HcServiceInfo serviceInfo, ref int irow)
{
var inDto = new HcDTOInfo();
inDto.Caption = serviceInfo.Caption + "的InDTO";
var range = (Range)sheet.Cells[19, 2];
inDto.Name = range.Value.ToString();
range = (Range)sheet.Cells[20, 15];
var cellValue = range.Value;
inDto.FieldArray = new List<HcFieldInfo>();
while (cellValue != null && !string.IsNullOrEmpty(cellValue.ToString()))
{
var field = new HcFieldInfo();
field.name = cellValue.ToString();
range = (Range)sheet.Cells[irow, 3];
field.caption = range.Value.ToString();
range = (Range)sheet.Cells[irow, 22];
field.FieldTypeString = range.Value.ToString();
inDto.FieldArray.Add(field);
irow += 1;
range = (Range)sheet.Cells[irow, 15];
cellValue = range.Value;
}
serviceInfo.InDTO = inDto;
}
private static void Main(string[] args)
{
DateTime today = DateTime.Now;
System.Data.DataTable dataCenso = new System.Data.DataTable();
List <Censo> censos = new List <Censo>();
WebRequest request = WebRequest.Create(URL);
try
{
using (var twitpicResponse = (HttpWebResponse)request.GetResponse())
{
using (var reader = new StreamReader(twitpicResponse.GetResponseStream()))
{
JsonSerializer json = new JsonSerializer();
var objText = reader.ReadToEnd();
censos = JsonConvert.DeserializeObject <List <Censo> >(objText);
foreach (var obj in censos)
{
obj.tempo = BlankFunction(obj.tempo);
obj.tempo = BlankFunctionTempo(obj.tempo);
}
dataCenso = CreateDataTable(censos);
}
}
}
catch (Exception ex)
{
String error = ex.Message;
Console.ReadKey();
}
String excelFilePath = "\\\\hspmins2\\NIR_Nucleo_Interno_Regulacao\\2359\\Censo" + today.ToString().Replace('/', '_').Replace(' ', '_').Replace(':', '_');
try
{
if (dataCenso == null || dataCenso.Columns.Count == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = app.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet workSheet = (Worksheet)app.ActiveSheet;
// load excel, and create a new workbook
//var excelApp = new Microsoft.Office.Interop.Excel.Application();
//excelApp.Workbooks.Add(excelApp);
// single worksheet
// Microsoft.Office.Interop.Excel._Worksheet workSheet = (Microsoft.Office.Interop.Excel._Worksheet)excelApp.ActiveSheet;
// column headings
for (var i = 0; i < dataCenso.Columns.Count; i++)
{
workSheet.Cells[1, i + 1] = dataCenso.Columns[i].ColumnName;
}
// rows
for (var i = 0; i < dataCenso.Rows.Count; i++)
{
// to do: format datetime values before printing
for (var j = 0; j < dataCenso.Columns.Count; j++)
{
/* if (j==9 || j == 10 || j == 13 || j == 24 || j == 25 )
* {
* var dt = dataCenso.Rows[i][j];
* workSheet.Cells[i + 2, j + 1] = Convert.ToDateTime(dataCenso.Rows[i][j]);
*
* }
* else
* {*/
workSheet.Cells[i + 2, j + 1] = dataCenso.Rows[i][j];
// }
}
}
// check file path
if (!string.IsNullOrEmpty(excelFilePath))
{
try
{
//workSheet.Name = "Censo" + today.ToString().Replace('/', '_');
workSheet.Name = "Censo";
workSheet.SaveAs(excelFilePath, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, XlSaveAsAccessMode.xlNoChange,
XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
app.Quit();
Console.WriteLine("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else
{ // no file path is given
app.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public Worksheet Write_Result_Excel(Worksheet ws, string result, int row)
{
Range rng = null;
Range rngErrormessage = null;
object missing = Type.Missing;
string screenshothref = "";
FileHandler file = new FileHandler();
DateTime now = DateTime.Now;
string date = now.ToString();
date = date.Replace(":", "-");
date = date.Replace("/", "-");
date = date.Replace(" ", "-");
FileHandler CurPath = new FileHandler();
string c = Directory.GetCurrentDirectory();
var stringSeparators = new[] { "bin" };
string[] str1 = c.Split(stringSeparators, StringSplitOptions.None);
string imagepath = Parallel_Quest.ImagePath;
try
{
rng = ws.get_Range("F" + row, missing);
rng.Font.Bold = true;
string StepName = ws.Name + row.ToString();
if ((result.ToLower() == "pass"))
{
rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
rng.Value2 = result.ToUpper();
}
else if ((result.ToLower() == "fail"))
{
screenshothref = Take_Screenshot(ws.Name, row, "fail", imagepath);
rng.Hyperlinks.Add(rng, screenshothref);
rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
rng.Value2 = "FAIL";
rngErrormessage = ws.get_Range("G" + row, missing);
rngErrormessage.Value2 = result.ToUpper();
}
else if ((result.ToLower() != "fail"))
{
screenshothref = Take_Screenshot(ws.Name, row, "fail", imagepath);
rng.Hyperlinks.Add(rng, screenshothref);
rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
rng.Value2 = "FAIL";
rngErrormessage = ws.get_Range("G" + row, missing);
rngErrormessage.Value2 = result.ToLower();
}
return(ws);
}
catch (System.Exception)
{
Marshal.FinalReleaseComObject(rng);
return(null);
}
finally
{
Marshal.FinalReleaseComObject(rng);
}
}
public static void QWSetup(Workbook WB)
{
#region Setup
// Create the Worksheet
Worksheet QW = WB.Worksheets.Add();
QW.Name = "Quotation Worksheet";
QW.Activate();
WB.Windows[1].Zoom = 130;
// Set defaults
QW.Cells.Font.Name = "Ebrima";
QW.Cells.Font.Size = 10;
// Sets the widths of columns A through X
double[] ColumnWidths = new double[] {
4.43, 9.29, 9.29, 5.00, 7.86, 2.29, // A : F
4.57, 7.43, 5.00, 5.00, 5.86, 3.29, // G : L
4.00, 4.86, 5.14, 1.14, 1.14, 5.43, // M : R
1.29, 6.71, 6.71, 4.00, 4.00, 4.00 // S : X
};
// TODO: Set ActiveRange scope to be determined by data source
Range ActiveRange_Start = QW.Range["A1"];
Range ActiveRange_End = QW.Range["X42"];
// Header <- Variable heights, # of rows, used on multiple pages
double[] HeaderHeights = new double[]
{
22.50, 15.00, 12.75, 18.00, 6.00, 18.00, 6.00
};
// Markup <- Variable heights, # of rows, used on first page only
double[] MarkupHeights = new double[]
{
15.00, 14.25, 14.25, 14.25, 14.25, 14.25, // 8 : 13
14.25, 14.25, 14.25, 14.25, 14.25, 14.25, // 14 : 19
14.25, 14.25, 14.25, 14.25, 6.00 // 20 : 24
};
// Materials List Header <- Set height, 1 row, used on first page only
// Materials List Column Headers <- Set heights, 2 rows, used on multiple pages
double[] MaterialsListHeights = new double[] { 18.00, 14.25, 15.75 };
// Materials List <- static height @ 14.25pts, dynamic # of rows, expanded to multiple pages if necessary
double MaterialsList_StdHeight = 14.25;
Range QWActiveRange = QW.Range["A1", "X42"];
SetColumnWidths(QWActiveRange, ColumnWidths);
SetHeaderRows(QWActiveRange, HeaderHeights);
SetMarkupRows(QWActiveRange, MarkupHeights, 8);
SetMaterialsListHeaders(QWActiveRange, MaterialsListHeights, 25);
SetMaterialsList(QWActiveRange, 15, 28, MaterialsList_StdHeight);
#endregion Setup
#region Header Object Declarations
// Set Header Objects
Shape APC_Logo = QW.Shapes.AddPicture(LogoPath,
Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue,
0.00F, 0.00F, 227.52F, 54F);
Range Title = QW.Range["I1", "O1"];
Range Version = QW.Range["I2", "O2"];
Range CfdBanner = QW.Range["S1", "X1"];
Range ApproverLbl = QW.Range["U2", "V2"];
Range ApproverTxt = QW.Range["W2", "X2"];
Range PageLbl = QW.Range["U4"];
Range PageTxt = QW.Range["V4", "X4"];
Range JobNameLbl = QW.Range["A6", "B6"];
Range JobNameTxt = QW.Range["C6", "G6"];
Range JobNumLbl = QW.Range["H6", "I6"];
Range JobNumTxt = QW.Range["J6", "K6"];
Range SalesLbl = QW.Range["L6"];
Range SalesTxt = QW.Range["M6", "N6"];
Range PMLbl = QW.Range["O6"];
Range PMTxt = QW.Range["P6", "R6"];
Range DateLbl = QW.Range["S6", "T6"];
Range DateTxt = QW.Range["U6", "X6"];
#endregion Header Object Declarations
#region Header Formatting
// Format Header Objects
APC_Logo.PictureFormat.Contrast = 0.6F;
#region Title
Title.Merge();
Title.Font.Size = 9;
Title.Font.Bold = true;
Title.Font.Italic = true;
AlignRange(Title, V: Align.VCenter);
// TODO: Set value to be dynamically adjusted based on data source.
Title.Value = "Commercial Quotation Worksheet {C#}";
#endregion Title
#region Version
Version.Merge();
Version.Font.Superscript = true;
AlignRange(Version, V: Align.VTop);
// TODO: Set value to be dynamically adjusted based on data source.
Version.Value = "Version: 0.0.1";
#endregion Version
#region Cfd Banner
CfdBanner.Merge();
CfdBanner.Borders.Weight = XlBorderWeight.xlMedium;
CfdBanner.Borders.Color = Black;
CfdBanner.Font.Bold = true;
CfdBanner.Font.Color = White;
CfdBanner.Interior.Color = APC_Blue;
AlignRange(CfdBanner, V: Align.VCenter);
CfdBanner.Value = "CONFIDENTIAL DOCUMENT";
#endregion Cfd Banner
#region Approver
ApproverLbl.Merge();
ApproverLbl.Font.Size = 8;
ApproverLbl.Font.Bold = true;
AlignRange(ApproverLbl, H: Align.HRight);
ApproverLbl.Value = "Approved By:";
ApproverTxt.Merge();
ApproverTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
AlignRange(ApproverTxt);
#endregion Approver
#region Page Number
PageLbl.Font.Size = 8;
PageLbl.Font.Bold = true;
PageLbl.HorizontalAlignment = XlHAlign.xlHAlignRight;
PageLbl.Value = "Page";
PageTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
PageTxt.HorizontalAlignment = XlHAlign.xlHAlignCenter;
PageTxt[1, 1].Value = 1;
PageTxt[1, 2].Font.Size = 8;
PageTxt[1, 2].Font.Bold = true;
PageTxt[1, 2].Value = "of";
// TODO: Dynamically set Page # based on data source.
PageTxt[1, 3].Value = 1;
#endregion Page Number
#region Job Bar
JobNameLbl.Merge();
JobNameLbl.Font.Bold = true;
AlignRange(JobNameLbl, H: Align.HRight);
JobNameLbl.Value = "JOB NAME:";
JobNameTxt.Merge();
JobNameTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
AlignRange(JobNameTxt);
JobNumLbl.Merge();
JobNumLbl.Font.Bold = true;
AlignRange(JobNumLbl, H: Align.HRight);
JobNumLbl.Value = "JOB NO:";
JobNumTxt.Merge();
JobNumTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
AlignRange(JobNumTxt);
SalesLbl.Merge();
SalesLbl.Font.Bold = true;
AlignRange(SalesLbl, H: Align.HRight);
SalesLbl.Value = "SP:";
SalesTxt.Merge();
SalesTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
AlignRange(SalesTxt);
PMLbl.Merge();
PMLbl.Font.Bold = true;
AlignRange(PMLbl, H: Align.HRight);
PMLbl.Value = "PM:";
PMTxt.Merge();
PMTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
AlignRange(PMTxt);
DateLbl.Merge();
DateLbl.Font.Bold = true;
AlignRange(DateLbl, H: Align.HRight);
DateLbl.Value = "DATE:";
DateTxt.Merge();
DateTxt.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
AlignRange(DateTxt);
#endregion Job Bar
#endregion Header Formatting
#region Markup Formatting
#endregion Markup Formatting
#region Materials List Formatting
#endregion Materials List Formatting
}
public void WriteOutputExcel()
{
output[0, 0] = Format.headervalue[0, 0];
for (int i = 0; i < 63; i++)
{
output[1, i] = Format.headervalue[1, i];
}
int k = 2;
for (int i = 0; i < DataMerge.destrow; i++)
{
for (int j = 0; j < 63; j++)
{
output[k, j] = Format.destinationfile[i, j];
}
k++;
}
string fileName = System.IO.Path.GetFileName(Program.sourcepath);
var extension = Path.GetExtension(fileName);
var fname = Path.GetFileNameWithoutExtension(fileName);
if (!System.IO.Directory.Exists(Program.destinationfolder))
{
System.IO.Directory.CreateDirectory(Program.destinationfolder);
}
destinationpath = Program.destinationfolder + "Output_ " + Program.fileno + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
try
{
merged_excel = new Application();
merged_wb = merged_excel.Workbooks.Add(1);
merged_excel.DisplayAlerts = false;
merged_ws = merged_wb.Worksheets[1];
merged_xlRange = merged_ws.UsedRange;
int merged_cols = merged_xlRange.Columns.Count;
merged_cols = Format.formatNumCols;
var startCell = (Range)merged_ws.Cells[1, 1];
var endCell = (Range)merged_ws.Cells[DataMerge.destrow + 2, merged_cols];
var writeRange = merged_ws.Range[startCell, endCell];
var columnHeadingsRange = merged_ws.Range[merged_ws.Cells[1, 1], merged_ws.Cells[1, merged_cols]];
columnHeadingsRange.Interior.Color = 0x9C632A;
columnHeadingsRange.Font.Color = XlRgbColor.rgbWhite;
columnHeadingsRange.Font.Size = 13;
var columnHeadingsRangeHeader = merged_ws.Range[merged_ws.Cells[2, 1], merged_ws.Cells[2, merged_cols]];
columnHeadingsRangeHeader.Interior.Color = 0xD9D9D9;
columnHeadingsRangeHeader.Font.Color = XlRgbColor.rgbBlack;
columnHeadingsRangeHeader.Font.Size = 12;
columnHeadingsRangeHeader.EntireRow.Font.Bold = true;
columnHeadingsRangeHeader.Borders.Color = XlRgbColor.rgbBlack;
columnHeadingsRange.EntireColumn.AutoFit();
columnHeadingsRangeHeader.Cells.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
var columnHeadingsRangeData = merged_ws.Range[merged_ws.Cells[3, 1], merged_ws.Cells[DataMerge.destrow + 2, merged_cols]];
columnHeadingsRangeData.Font.Color = XlRgbColor.rgbBlack;
columnHeadingsRangeData.Font.Size = 11;
columnHeadingsRangeData.Borders.Color = XlRgbColor.rgbBlack;
var columnHeadingsRangeEmail = merged_ws.Range[merged_ws.Cells[3, 11], merged_ws.Cells[DataMerge.destrow + 2, 11]];
columnHeadingsRangeEmail.Font.Color = XlRgbColor.rgbBlue;
writeRange.Value = output;
merged_wb.SaveAs(destinationpath);
merged_wb.Close();
}
catch (Exception e)
{
Program.continuexecution = false;
Console.WriteLine("Can't create target file because of these reasons:");
Console.WriteLine(e.Message);
Program.CloseAllExcelAppication(merged_ws, merged_wb, merged_excel);
}
finally
{
Program.CloseAllExcelAppication(merged_ws, merged_wb, merged_excel);
}
// AutoFit Columns
AutoFitRowColumn fit = new AutoFitRowColumn();
fit.FittingRowColumn(destinationpath);
Program.fileno++;
}
/// <summary>
/// Launch the ontology import wizard and generate an Excel skeleton from the ontology.
/// </summary>
public void LoadOntology()
{
this.resourcesToImport.Clear();
this.nestedProperties.Clear();
// Displays an OpenFileDialog so the user can select an ontology.
OpenFileDialog openOntologyFileDialog = new OpenFileDialog();
openOntologyFileDialog.Filter = "RDF/XML (*.rdf)|*.rdf|Turtle (*.ttl)|*.ttl|JSON-LD (*.jsonld)|*.jsonld|NTriples (*.nt)|*.nt|NQuads (*.nq)|*.nq|TriG (*.trig)|*.trig";
openOntologyFileDialog.Title = "Select an ontology file";
// Show the Dialog.
// If the user clicked OK in the dialog and an OWL file was selected, open it.
if (openOntologyFileDialog.ShowDialog() == DialogResult.OK)
{
OntologyGraph g = new OntologyGraph();
FileLoader.Load(g, openOntologyFileDialog.FileName);
ImportOptionsForm importOptionsForm = new ImportOptionsForm(g);
if (importOptionsForm.ShowDialog() == DialogResult.OK)
{
// Iterate through the named bottom classes; generate one worksheet for each
foreach (OntologyClass oClass in g.OwlClasses)
{
if (oClass.Resource.NodeType == NodeType.Uri && resourcesToImport.Contains(oClass.ToString()))
{
Worksheet newWorksheet = Globals.ThisAddIn.Application.Worksheets.Add();
UriNode classAsUriNode = (UriNode)oClass.Resource;
newWorksheet.Name = Helper.GetLocalName(classAsUriNode.Uri);
// Start iterating from the first column
int column = 1;
// Add column for the IRI identifier
// <IRI> is a special identifier used for this purpose, signaling that a) the IRI shall
// be minted from this column, and b) the subsequent row will contain the OWL class for all minted entities
string identifierColumnName = Helper.GetExcelColumnName(column);
string identifierColumnHeaderCellIdentifier = String.Format("{0}1", identifierColumnName);
Range identifierColumnHeaderCell = newWorksheet.get_Range(identifierColumnHeaderCellIdentifier);
identifierColumnHeaderCell.Value = "Identifier";
string identifierNote = "<IRI>";
identifierNote += String.Format("\n<{0}>", classAsUriNode.Uri.ToString());
identifierColumnHeaderCell.NoteText(identifierNote);
column++;
// Iterate through the properties for which this class is in the domain;
// generate one column for each property (named from label and if that does not exist from IRI)
// Order the columns by type, with datatype properties coming before object properties,
// then by string representation
foreach (OntologyProperty oProperty in oClass.IsDomainOf.OrderBy(o => o.Types.First()).OrderBy(o => o.ToString()))
{
if (oProperty.Resource.NodeType == NodeType.Uri && resourcesToImport.Contains(oProperty.ToString()))
{
// This is because Excel uses strange adressing, i.e., "A1" instead of something
// numeric and zero-indexed such as "0,0".
string headerColumnName = Helper.GetExcelColumnName(column);
string headerCellIdentifier = String.Format("{0}1", headerColumnName);
Range headerCellRange = newWorksheet.get_Range(headerCellIdentifier);
UriNode propertyAsUriNode = (UriNode)oProperty.Resource;
// TODO: the below code is extremely repetitive. Sometime, when not sick and brain is working better,
// Future Karl will refactor and simplify this (hopefully)
if (nestedProperties.Keys.Contains(propertyAsUriNode.Uri.AbsoluteUri))
{
foreach (string nestedPropertyUri in nestedProperties[propertyAsUriNode.Uri.AbsoluteUri])
{
// Repeat header cell selection for each nested property
headerColumnName = Helper.GetExcelColumnName(column);
headerCellIdentifier = String.Format("{0}1", headerColumnName);
headerCellRange = newWorksheet.get_Range(headerCellIdentifier);
// Find and assign label
string headerLabel;
// Assign property IRI
string noteText = String.Format("<{0}>", propertyAsUriNode.Uri.ToString());
// Asign property type hinting
string propertyType = oProperty.Types.First().ToString();
noteText += String.Format("\n<{0}>", propertyType);
// Assign range hinting IRI
// TODO: what if no range exists? see same case below and after else clause
OntologyClass[] namedRanges = oProperty.Ranges.Where(o => o.Resource.NodeType == NodeType.Uri).ToArray();
if (namedRanges.Count() > 0)
{
UriNode rangeAsUriNode = (UriNode)namedRanges.First().Resource;
string rangeUri = rangeAsUriNode.Uri.ToString();
noteText += String.Format("\n<{0}>", rangeUri);
}
// Branching for special case of rdfs:label
if (nestedPropertyUri.Equals(OntologyHelper.PropertyLabel))
{
// Assign header label
headerLabel = "rdfs:label";
// Nested property IRI (i.e., rdfs:label)
noteText += String.Format("\n<{0}>", OntologyHelper.PropertyLabel);
// Nested property type
noteText += String.Format("\n<{0}>", OntologyHelper.OwlAnnotationProperty);
// Nested property range
noteText += String.Format("\n<{0}>", XmlSpecsHelper.XmlSchemaDataTypeString);
}
else
{
// Get the property from the ontology
OntologyProperty nestedProperty = g.OwlProperties.Where(property => ((UriNode)property.Resource).Uri.AbsoluteUri.Equals(nestedPropertyUri)).First();
UriNode nestedPropertyAsUriNode = (UriNode)nestedProperty.Resource;
// Assign header label
if (nestedProperty.Label.Count() > 0)
{
ILiteralNode labelNode = nestedProperty.Label.First();
headerLabel = labelNode.Value;
}
else
{
headerLabel = Helper.GetLocalName(nestedPropertyAsUriNode.Uri);
}
// Nested property IRI
noteText += String.Format("\n<{0}>", nestedPropertyAsUriNode.Uri.ToString());
// Asign nested property type hinting
string nestedPropertyType;
if (nestedProperty.Types.Count() > 0)
{
nestedPropertyType = nestedProperty.Types.First().ToString();
}
else
{
nestedPropertyType = "";
}
noteText += String.Format("\n<{0}>", nestedPropertyType);
// Nested range hinting IRI
OntologyClass[] namedNestedRanges = nestedProperty.Ranges.Where(o => o.Resource.NodeType == NodeType.Uri).ToArray();
string nestedRange;
if (namedNestedRanges.Count() > 0)
{
nestedRange = ((UriNode)namedNestedRanges.First().Resource).Uri.ToString();
}
else
{
nestedRange = "";
}
noteText += String.Format("\n<{0}>", nestedRange);
}
// Assign header label
headerLabel = headerLabel + " (through " + Helper.GetLocalName(propertyAsUriNode.Uri) + ")";
headerCellRange.Value = headerLabel;
// Assign note text
headerCellRange.AddComment(noteText);
column++;
}
}
else
{
// Find and assign label
string propertyLabel;
if (oProperty.Label.Count() > 0)
{
ILiteralNode labelNode = oProperty.Label.First();
propertyLabel = labelNode.Value;
}
else
{
propertyLabel = Helper.GetLocalName(propertyAsUriNode.Uri);
}
headerCellRange.Value = propertyLabel;
// Assign property IRI
string noteText = String.Format("<{0}>", propertyAsUriNode.Uri.ToString());
// Asign property type hinting
string propertyType = oProperty.Types.First().ToString();
noteText += String.Format("\n<{0}>", propertyType);
// Assign range hinting IRI (provided simple )
OntologyClass[] namedRanges = oProperty.Ranges.Where(o => o.Resource.NodeType == NodeType.Uri).ToArray();
if (namedRanges.Count() > 0)
{
UriNode rangeAsUriNode = (UriNode)namedRanges.First().Resource;
string rangeUri = rangeAsUriNode.Uri.ToString();
noteText += String.Format("\n<{0}>", rangeUri);
}
// Assign note text
headerCellRange.AddComment(noteText);
column++;
}
}
}
// Bold the header row and fit the columns so things look nice
Range headerRow = newWorksheet.get_Range("A1").EntireRow;
headerRow.Font.Bold = true;
headerRow.Columns.AutoFit();
}
}
}
}
}
private void ButtonCreateExcel_Click(object sender, RoutedEventArgs e)
{
//папка с exe встроенная
string BasePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
// Поиск стандартной папки и наименования документа для открытия в формате Excel
string query_folder = "select receipt_of_materials.Default_Folder, receipt_of_materials.Name_Of_Document " +
"from receipt_of_materials where receipt_of_materials.id_Document_Of_Receipt = @DocumentID ";
MySqlCommand command_folder = new MySqlCommand(query_folder, connection);
command_folder.Parameters.AddWithValue("@DocumentID", DocumentId);
string Default_Folder = "";
string Name_Of_Document = "";
using (DbDataReader reader = command_folder.ExecuteReader())
{
while (reader.Read())
{
Default_Folder = reader.GetString(0);
Name_Of_Document = reader.GetString(1);
}
}
Excel.Application excel;
// Вот если сделать именно так, то будет проверка на запуск Экселя, и если все ок, то идем дальше, иначе сразу вылетаем из метода
try
{
excel = new Excel.Application();
}
catch
{
MessageBox.Show("На вашем компьютере не установлен Excel. Печать невозможна.", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
// Стоял здесь false, нужно оттестить!!!
excel.Visible = true;
// Здесь наверно генерация данных в таблицы Экселя
Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
Worksheet sheet1 = (Worksheet)workbook.Sheets[1];
excel.DisplayAlerts = false;
for (int j = 0; j < receiptrecordGrid.Columns.Count; j++)
{
Range myRange = (Range)sheet1.Cells[1, j + 1];
sheet1.Cells[1, j + 1].Font.Bold = true;
sheet1.Columns[j + 1].ColumnWidth = 15;
myRange.Value2 = receiptrecordGrid.Columns[j].Header;
}
for (int i = 0; i < receiptrecordGrid.Columns.Count; i++)
{
MessageBox.Show("На вашем компьютере не установлен Excel. Печать невозможна.", "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
for (int j = 0; j < receiptrecordGrid.Items.Count; j++)
{
TextBlock b = receiptrecordGrid.Columns[i].GetCellContent(receiptrecordGrid.Items[j]) as TextBlock;
Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[j + 2, i + 1];
myRange.Value2 = b.Text;
}
}
// Сохранение файла
System.IO.Directory.CreateDirectory(BasePath + "\\" + Default_Folder);
workbook.SaveAs(BasePath + "\\" + Default_Folder + "\\" + Name_Of_Document + ".xls", Excel.XlFileFormat.xlWorkbookNormal);
workbook.Close(true);
excel.Quit();
MessageBox.Show("Документ " + Name_Of_Document + " создан успешно.\n" + "Путь документа: " + BasePath + "\\" + Default_Folder);
}
public Workbook DashBoard_Update(string path, Workbook wb, int row, List <string> result)
{
string sheet = "DashBoard";
string screenshothref = "";
Worksheet ws = null;
Range rng1 = null;
Range rng2 = null;
Range rng3 = null;
Range rng4 = null;
Range rng = null;
FileHandler file = new FileHandler();
object missing = Type.Missing;
DateTime now = DateTime.Now;
string date = now.ToString();
date = date.Replace(":", "-");
date = date.Replace("/", "-");
date = date.Replace(" ", "-");
FileHandler CurPath = new FileHandler();
string c = Directory.GetCurrentDirectory();
var stringSeparators = new[] { "bin" };
string[] str1 = c.Split(stringSeparators, StringSplitOptions.None);
string imagepath = Parallel_Quest.ImagePath;
try
{
ws = (Worksheet)wb.Sheets[sheet];
ws.Select(Type.Missing);
rng1 = ws.get_Range("E" + row, missing);
rng1.Value2 = result[3];
rng1.Font.Bold = true;
string StepName = ws.Name + row.ToString();
if ((result[3].ToLower() == "pass"))
{
rng1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
else if ((result[3].ToLower() == "fail"))
{
screenshothref = Take_Screenshot(sheet, row, "fail", imagepath);
// rng.Hyperlinks.Add(rng, screenshothref);
rng1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
rng2 = ws.get_Range("F" + row, missing);
rng2.Value2 = result[0];
rng2.Font.Bold = true;
rng3 = ws.get_Range("G" + row, missing);
rng3.Value2 = result[1];
rng3.Font.Bold = true;
rng3.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
rng4 = ws.get_Range("H" + row, missing);
rng4.Value2 = result[2];
rng4.Font.Bold = true;
rng4.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
string tmpName = Path.GetTempFileName();
File.Delete(tmpName);
wb.SaveAs(tmpName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(rng1);
Marshal.FinalReleaseComObject(rng2);
Marshal.FinalReleaseComObject(rng3);
Marshal.FinalReleaseComObject(rng4);
// Marshal.FinalReleaseComObject(ws);
File.Delete(path);
File.Copy(tmpName, path);
return(wb);
}
catch (Exception)
{
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(rng1);
Marshal.FinalReleaseComObject(rng2);
Marshal.FinalReleaseComObject(rng3);
Marshal.FinalReleaseComObject(rng4);
// Marshal.FinalReleaseComObject(ws);
return(null);
}
}
public void WriteExcel(string path, string sheet, int row, string result)
{
string screenshothref = "";
Application excel = null;
Workbook wb = null;
Worksheet ws = null;
Range rng = null;
FileHandler file = new FileHandler();
object missing = Type.Missing;
DateTime now = DateTime.Now;
string date = now.ToString();
date = date.Replace(":", "-");
date = date.Replace("/", "-");
date = date.Replace(" ", "-");
FileHandler CurPath = new FileHandler();
string c = Directory.GetCurrentDirectory();
var stringSeparators = new[] { "bin" };
string[] str1 = c.Split(stringSeparators, StringSplitOptions.None);
string imagepath = Parallel_Quest.ImagePath;
try
{
excel = new Application();
//If I use Open or _Open it gives the same
wb = excel.Workbooks.Open(path,
missing, //updatelinks
false, //readonly
missing, //format
missing, //Password
missing, //writeResPass
true, //ignoreReadOnly
missing, //origin
missing, //delimiter
true, //editable
missing, //Notify
missing, //converter
missing, //AddToMru
missing, //Local
missing); //corruptLoad
ws = (Worksheet)wb.Sheets[sheet];
ws.Select(Type.Missing);
rng = ws.get_Range("F" + row, missing);
rng.Value2 = result.ToUpper();
rng.Font.Bold = true;
string StepName = ws.Name + row.ToString();
if ((result.ToLower() == "pass"))
{
rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
else if ((result.ToLower() == "fail"))
{
screenshothref = Take_Screenshot(sheet, row, "fail", imagepath);
rng.Hyperlinks.Add(rng, screenshothref);
rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
string tmpName = Path.GetTempFileName();
File.Delete(tmpName);
wb.SaveAs(tmpName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
wb.Close(false, missing, missing);
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(rng);
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(wb);
File.Delete(path);
File.Move(tmpName, path);
excel.Quit();
Marshal.FinalReleaseComObject(excel);
}
catch (Exception)
{
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(rng);
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(wb);
excel.Quit();
Marshal.FinalReleaseComObject(excel);
}
}
private XLRow RowShift(Int32 rowsToShift)
{
return(Worksheet.Row(RowNumber() + rowsToShift));
}
/// <summary>
/// Write excel file of a list of object as T
/// Assume that maximum of 24 columns
/// </summary>
/// <typeparam name="T">Object type to pass in</typeparam>
/// <param name="fileName">Full path of the file name of excel spreadsheet</param>
/// <param name="objects">list of the object type</param>
/// <param name="sheetName">Sheet names of Excel File</param>
/// <param name="headerNames">Header names of the object</param>
public void Create <T>(
string fileName,
List <T> objects,
string sheetName,
List <string> headerNames)
{
//Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
Workbook workbook = new Workbook();
FileVersion fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart);
Worksheet worksheet = new Worksheet();
//PageMargins pageM = worksheet.GetFirstChild<PageMargins>();
//SheetProtection sheetProtection = new SheetProtection();
//sheetProtection.Sheet = true;
//sheetProtection.Objects = true;
//sheetProtection.Scenarios = true;
////add column C:Z to allow edit range, which means column A,B and after Z are locked
//ProtectedRanges pRanges = new ProtectedRanges();
//ProtectedRange pRange = new ProtectedRange();
//ListValue<StringValue> lValue = new ListValue<StringValue>();
//lValue.InnerText = "D1:Z1048576";
//pRange.SequenceOfReferences = lValue;
//pRange.Name = "AllowEditRange1";
//pRanges.Append(pRange);
//worksheet.InsertBefore(sheetProtection, pageM);
//worksheet.InsertBefore(pRanges, pageM);
int numCols = headerNames.Count;
int width = headerNames.Max(h => h.Length) + 5;
Columns columns = new Columns();
for (int col = 0; col < numCols; col++)
{
Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
if (col == 0)
{
c.Hidden = BooleanValue.FromBoolean(true);
}
columns.Append(c);
}
worksheet.Append(columns);
Sheets sheets = new Sheets();
Sheet sheet = new Sheet {
Name = sheetName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
/// <summary>
/// 激活工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
public void ActivateSheet(string sheetName)
{
Worksheet worksheet = (Worksheet)myExcel.Worksheets[sheetName];
worksheet.Activate();
}
/// <summary>
/// 激活工作表
/// </summary>
/// <param name="sheetNum">工作表序号</param>
public void ActivateSheet(int sheetNum)
{
Worksheet worksheet = (Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Activate();
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="sheetNum">工作表序号，从左到右，从开始</param>
/// <param name="newSheetName">新的工作表名</param>
public void ReNameSheet(int sheetNum, string newSheetName)
{
Worksheet worksheet = (Worksheet)myExcel.Worksheets[sheetNum];
worksheet.Name = newSheetName;
}
/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="oldSheetName">原有工作表名</param>
/// <param name="newSheetName">新的工作表名</param>
public void ReNameSheet(string oldSheetName, string newSheetName)
{
Worksheet worksheet = (Worksheet)myExcel.Worksheets[oldSheetName];
worksheet.Name = newSheetName;
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Create a Workbook.
Workbook wbk = new Workbook();
// Create a Worksheet and get the first sheet.
Worksheet worksheet = wbk.Worksheets[0];
// Create a Cells object ot fetch all the cells.
Cells cells = worksheet.Cells;
// Merge some Cells (C6:E7) into a single C6 Cell.
cells.Merge(5, 2, 2, 3);
// Input data into C6 Cell.
worksheet.Cells[5, 2].PutValue("This is my value");
// Create a Style object to fetch the Style of C6 Cell.
Style style = worksheet.Cells[5, 2].GetStyle();
// Create a Font object
Font font = style.Font;
// Set the name.
font.Name = "Times New Roman";
// Set the font size.
font.Size = 18;
// Set the font color
font.Color = System.Drawing.Color.Blue;
// Bold the text
font.IsBold = true;
// Make it italic
font.IsItalic = true;
// Set the backgrond color of C6 Cell to Red
style.ForegroundColor = System.Drawing.Color.Red;
style.Pattern = BackgroundType.Solid;
// Apply the Style to C6 Cell.
cells[5, 2].SetStyle(style);
// Save the Workbook.
wbk.Save(dataDir + "mergingcells.out.xls");
// ExEnd:1
}
private void btn导入_Click(object sender, EventArgs e)
{
if (prevMonth.Year != Convert.ToInt32(year.Value) || prevMonth.Month != Convert.ToInt32(month.Text))
{
if (MessageBox.Show("您导入的不是上月的数据，您要继续吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2, 0, false) != DialogResult.Yes)
{
return;
}
}
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
CreateWaitDialog("正在导入数据...", "请稍等");
Workbook workbook = new Workbook(openFileDialog1.FileName);
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
currRows.Clear();
int totalCount = 0;
List <Row> errorRows = new List <Row>();
foreach (Row row in sheet.Cells.Rows)
{
try
{
OtherMoneyData item = new OtherMoneyData();
item.年 = Convert.ToInt32(year.Value);
item.月 = Convert.ToInt32(month.Text);
item.员工编号 = (string)cells[row.Index, 0].StringValue;
item.姓名 = (string)cells[row.Index, 1].StringValue;
item.类型 = (string)cells[row.Index, 2].StringValue;
item.项目名称 = (string)cells[row.Index, 3].StringValue;
item.金额 = Convert.ToDecimal(cells[row.Index, 4].Value);
currRows.Add(item);
totalCount++;
}
catch
{
errorRows.Add(row);
}
}
CloseWaitDialog();
gridControl1.RefreshDataSource();
string errMsg = "";
foreach (Row row in errorRows)
{
if (errMsg != "")
{
errMsg += "、";
}
errMsg += (row.Index + 1).ToString();
}
string msg = "导入完毕，" + totalCount + " 成功, " + errorRows.Count + " 失败。\n\n失败行：" + errMsg;
MessageBox.Show(msg);
}
}
public byte[] GetZXListToFile(string yhm, string xm, string isrelease)
{
using (DBConnection dbc = new DBConnection())
{
try
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells; //单元格
//样式2
Style style2 = workbook.Styles[workbook.Styles.Add()];
style2.HorizontalAlignment = TextAlignmentType.Left; //文字居中
style2.Font.Name = "宋体"; //文字字体
style2.Font.Size = 14; //文字大小
style2.Font.IsBold = true; //粗体
style2.IsTextWrapped = true; //单元格内容自动换行
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
style2.IsLocked = true;
//样式3
Style style4 = workbook.Styles[workbook.Styles.Add()];
style4.HorizontalAlignment = TextAlignmentType.Left; //文字居中
style4.Font.Name = "宋体"; //文字字体
style4.Font.Size = 11; //文字大小
style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
cells.SetRowHeight(0, 20);
cells[0, 0].PutValue("专线名称");
cells[0, 0].SetStyle(style2);
cells.SetColumnWidth(0, 20);
cells[0, 1].PutValue("登录名");
cells[0, 1].SetStyle(style2);
cells.SetColumnWidth(1, 20);
cells[0, 2].PutValue("电话");
cells[0, 2].SetStyle(style2);
cells.SetColumnWidth(2, 20);
cells[0, 3].PutValue("线路");
cells[0, 3].SetStyle(style2);
cells.SetColumnWidth(3, 20);
cells[0, 4].PutValue("是否可以自行发布运费券");
cells[0, 4].SetStyle(style2);
cells.SetColumnWidth(4, 20);
cells[0, 5].PutValue("审核时间");
cells[0, 5].SetStyle(style2);
cells.SetColumnWidth(5, 20);
cells[0, 6].PutValue("开放次数");
cells[0, 6].SetStyle(style2);
cells.SetColumnWidth(6, 20);
string where = "";
if (!string.IsNullOrEmpty(yhm.Trim()))
{
where += " and " + dbc.C_Like("a.UserName", yhm.Trim(), LikeStyle.LeftAndRightLike);
}
if (!string.IsNullOrEmpty(xm.Trim()))
{
where += " and " + dbc.C_Like("a.UserXM", xm.Trim(), LikeStyle.LeftAndRightLike);
}
if (!string.IsNullOrEmpty(isrelease.Trim()))
{
where += " and " + dbc.C_EQ("a.IsCanRelease", Convert.ToInt32(isrelease));
}
string str = @"select a.*,b.fqcs from [tb_b_user] a
left join (select count(SaleRecordID) as fqcs,SaleRecordUserID from tb_b_salerecord where status=0 and SaleRecordLX!=0 group by SaleRecordUserID) b
on a.UserID=b.SaleRecordUserID
where a.IsSHPass=1 and a.ClientKind=1 " + where + @" order by a.AddTime desc,a.UserName,a.UserXM";
//开始取分页数据
System.Data.DataTable dt = dbc.ExecuteDataTable(str);
for (int i = 0; i < dt.Rows.Count; i++)
{
cells[i + 1, 0].PutValue(dt.Rows[i]["UserXM"]);
cells[i + 1, 0].SetStyle(style4);
cells[i + 1, 1].PutValue(dt.Rows[i]["UserName"]);
cells[i + 1, 1].SetStyle(style4);
if (dt.Rows[i]["UserTel"] != null && dt.Rows[i]["UserTel"].ToString() != "")
{
cells[i + 1, 2].PutValue(dt.Rows[i]["UserTel"]);
}
cells[i + 1, 2].SetStyle(style4);
var xl = "";
if (dt.Rows[i]["FromRoute"] != null && dt.Rows[i]["FromRoute"].ToString() != "")
{
xl += dt.Rows[i]["FromRoute"].ToString();
}
if (dt.Rows[i]["ToRoute"] != null && dt.Rows[i]["ToRoute"].ToString() != "")
{
xl += "─" + dt.Rows[i]["ToRoute"].ToString();
}
cells[i + 1, 3].PutValue(xl);
cells[i + 1, 3].SetStyle(style4);
var can = "";
if (dt.Rows[i]["IsCanRelease"] != null && dt.Rows[i]["IsCanRelease"].ToString() != "")
{
if (Convert.ToInt32(dt.Rows[i]["IsCanRelease"].ToString()) == 1)
{
can += "可以";
}
else
{
can += "不可以";
}
}
cells[i + 1, 4].PutValue(can);
cells[i + 1, 4].SetStyle(style4);
if (dt.Rows[i]["canReleaseTime"] != null && dt.Rows[i]["canReleaseTime"].ToString() != "")
{
cells[i + 1, 5].PutValue(Convert.ToDateTime(dt.Rows[i]["canReleaseTime"]).ToString("yyyy-MM-dd"));
}
cells[i + 1, 5].SetStyle(style4);
if (dt.Rows[i]["fqcs"] != null && dt.Rows[i]["fqcs"].ToString() != "")
{
cells[i + 1, 6].PutValue(dt.Rows[i]["fqcs"]);
}
cells[i + 1, 6].SetStyle(style4);
}
MemoryStream ms = workbook.SaveToStream();
byte[] bt = ms.ToArray();
return(bt);
}
catch (Exception ex)
{
throw ex;
}
}
}
private string _ExportToExcel(ConfigurationKpiAchievementsViewModel viewModel)
{
string dateFormat = "dd-mmm-yy";
string workSheetName = new StringBuilder(viewModel.PeriodeType).ToString();
switch (viewModel.PeriodeType)
{
case "Yearly":
dateFormat = "yyyy";
break;
case "Monthly":
dateFormat = "mmm-yy";
workSheetName = string.Format("{0}_{1}", workSheetName, viewModel.Year);
break;
default:
dateFormat = "dd-mmm-yy";
workSheetName = string.Format("{0}_{1}-{2}", workSheetName, viewModel.Year, viewModel.Month.ToString().PadLeft(2, '0'));
break;
}
string fileName = new StringBuilder(workSheetName).Append(".xls").ToString();
var path = System.Web.HttpContext.Current.Request.MapPath(TemplateDirectory + "/KpiAchievement/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string resultFilePath = System.Web.HttpContext.Current.Request.MapPath(string.Format("{0}/KpiAchievement/{1}", TemplateDirectory, fileName));
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = workSheetName;
workbook.Worksheets.ActiveWorksheet = worksheet;
RowCollection rows = workbook.Worksheets[0].Rows;
ColumnCollection columns = workbook.Worksheets[0].Columns;
Row HeaderRow = rows[0];
HeaderRow.FillColor = Color.DarkGray;
HeaderRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
HeaderRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
Column KpiIdColumn = columns[0];
Column KpiNameColumn = columns[1];
KpiIdColumn.Visible = false;
HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiIdColumn.Index].Value = "KPI ID";
HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiNameColumn.Index].Value = "KPI Name";
int i = 1; //i for row
foreach (var kpi in viewModel.Kpis)
{
worksheet.Cells[i, KpiIdColumn.Index].Value = kpi.Id;
worksheet.Cells[i, KpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement);
int j = 2; // for column
foreach (var achievement in kpi.KpiAchievements)
{
worksheet.Cells[HeaderRow.Index, j].Value = achievement.Periode;
worksheet.Cells[HeaderRow.Index, j].NumberFormat = dateFormat;
worksheet.Cells[HeaderRow.Index, j].AutoFitColumns();
worksheet.Cells[i, j].Value = achievement.Value;
worksheet.Cells[i, j].NumberFormat = "#,0.#0";
worksheet.Columns[j].AutoFitColumns();
j++;
}
Column TotalValueColumn = worksheet.Columns[j];
if (i == HeaderRow.Index + 1)
{
worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index].Value = "Average";
worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index + 1].Value = "SUM";
Range r1 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
}
else
{
// add formula
Range r2 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
}
i++;
}
KpiNameColumn.AutoFitColumns();
worksheet.FreezePanes(HeaderRow.Index, KpiNameColumn.Index);
using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
{
workbook.SaveDocument(stream, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
stream.Close();
}
//workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
//todo create file from viewModel
return(string.Format("{0}KpiAchievement/{1}", TemplateDirectory, fileName));
}
public byte[] GetZXList2ToFile(string yhm, string xm, string ispass)
{
using (DBConnection dbc = new DBConnection())
{
try
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells; //单元格
//样式2
Style style2 = workbook.Styles[workbook.Styles.Add()];
style2.HorizontalAlignment = TextAlignmentType.Left; //文字居中
style2.Font.Name = "宋体"; //文字字体
style2.Font.Size = 14; //文字大小
style2.Font.IsBold = true; //粗体
style2.IsTextWrapped = true; //单元格内容自动换行
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
style2.IsLocked = true;
//样式3
Style style4 = workbook.Styles[workbook.Styles.Add()];
style4.HorizontalAlignment = TextAlignmentType.Left; //文字居中
style4.Font.Name = "宋体"; //文字字体
style4.Font.Size = 11; //文字大小
style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
cells.SetRowHeight(0, 20);
cells[0, 0].PutValue("专线名称");
cells[0, 0].SetStyle(style2);
cells.SetColumnWidth(0, 20);
cells[0, 1].PutValue("登录名");
cells[0, 1].SetStyle(style2);
cells.SetColumnWidth(1, 20);
cells[0, 2].PutValue("电话");
cells[0, 2].SetStyle(style2);
cells.SetColumnWidth(2, 20);
cells[0, 3].PutValue("线路");
cells[0, 3].SetStyle(style2);
cells.SetColumnWidth(3, 20);
cells[0, 4].PutValue("简介");
cells[0, 4].SetStyle(style2);
cells.SetColumnWidth(4, 20);
cells[0, 5].PutValue("是否通过");
cells[0, 5].SetStyle(style2);
cells.SetColumnWidth(5, 20);
string _url = ServiceURL + "tbbuserapply.selectApply";
string jsonParam = new JavaScriptSerializer().Serialize(new
{
tradeCode = "tbbuserapply.selectApply",
status = ispass,
userid = "",
username = yhm,
userxm = xm,
//currentPage = 1,
//pageSize = 10,
closePagination = true
});
var request = (HttpWebRequest)WebRequest.Create(_url);
request.Method = "POST";
request.ContentType = "application/json;charset=UTF-8";
var byteData = Encoding.UTF8.GetBytes(jsonParam);
var length = byteData.Length;
request.ContentLength = length;
var writer = request.GetRequestStream();
writer.Write(byteData, 0, length);
writer.Close();
var response = (HttpWebResponse)request.GetResponse();
var responseString = new StreamReader(response.GetResponseStream(), Encoding.GetEncoding("utf-8")).ReadToEnd();
ToJsonMy2 ToJsonMy2 = JsonConvert.DeserializeObject <ToJsonMy2>(responseString); //将json数据转化为对象类型并赋值给list
zxlist[] list = ToJsonMy2.list;
if (list.Length > 0)
{
for (int i = 0; i < list.Length; i++)
{
cells[i + 1, 0].PutValue(list[i].UserXM);
cells[i + 1, 0].SetStyle(style4);
cells[i + 1, 1].PutValue(list[i].UserName);
cells[i + 1, 1].SetStyle(style4);
if (list[i].param.usertel != null && list[i].param.usertel != "")
{
cells[i + 1, 2].PutValue(list[i].param.usertel);
}
cells[i + 1, 2].SetStyle(style4);
var xl = "";
if (list[i].param.fromroute != null && list[i].param.fromroute != "")
{
xl += list[i].param.fromroute;
}
if (list[i].param.toroute != null && list[i].param.toroute != "")
{
xl += "─" + list[i].param.toroute;
}
cells[i + 1, 3].PutValue(xl);
cells[i + 1, 3].SetStyle(style4);
cells[i + 1, 4].PutValue(list[i].param.usercontent);
cells[i + 1, 4].SetStyle(style4);
var shzt = "";
if (list[i].status != null && list[i].status != "")
{
if (Convert.ToInt32(list[i].status) == 1)
{
shzt = "通过";
}
else if (Convert.ToInt32(list[i].status) == 2)
{
shzt = "拒绝";
}
else if (Convert.ToInt32(list[i].status) == 0)
{
shzt = "待审核";
}
}
else
{
shzt = "待审核";
}
cells[i + 1, 5].PutValue(shzt);
cells[i + 1, 5].SetStyle(style4);
}
}
MemoryStream ms = workbook.SaveToStream();
byte[] bt = ms.ToArray();
return(bt);
}
catch (Exception ex)
{
throw ex;
}
}
}
public byte[] getZFQListToFile(string userxm, string isVerifyType, string cx_beg, string cx_end)
{
using (DBConnection dbc = new DBConnection())
{
try
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells; //单元格
//样式2
Style style2 = workbook.Styles[workbook.Styles.Add()];
style2.HorizontalAlignment = TextAlignmentType.Left; //文字居中
style2.Font.Name = "宋体"; //文字字体
style2.Font.Size = 14; //文字大小
style2.Font.IsBold = true; //粗体
style2.IsTextWrapped = true; //单元格内容自动换行
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
style2.IsLocked = true;
//样式3
Style style4 = workbook.Styles[workbook.Styles.Add()];
style4.HorizontalAlignment = TextAlignmentType.Left; //文字居中
style4.Font.Name = "宋体"; //文字字体
style4.Font.Size = 11; //文字大小
style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
cells.SetRowHeight(0, 20);
cells[0, 0].PutValue("专线名称");
cells[0, 0].SetStyle(style2);
cells.SetColumnWidth(0, 20);
cells[0, 1].PutValue("线路");
cells[0, 1].SetStyle(style2);
cells.SetColumnWidth(1, 20);
cells[0, 2].PutValue("目的地");
cells[0, 2].SetStyle(style2);
cells.SetColumnWidth(2, 20);
cells[0, 3].PutValue("运费券");
cells[0, 3].SetStyle(style2);
cells.SetColumnWidth(3, 20);
cells[0, 4].PutValue("折扣");
cells[0, 4].SetStyle(style2);
cells.SetColumnWidth(4, 20);
cells[0, 5].PutValue("有效时间");
cells[0, 5].SetStyle(style2);
cells.SetColumnWidth(5, 20);
cells[0, 6].PutValue("开放时间");
cells[0, 6].SetStyle(style2);
cells.SetColumnWidth(6, 20);
cells[0, 7].PutValue("货物类型");
cells[0, 7].SetStyle(style2);
cells.SetColumnWidth(7, 20);
cells[0, 8].PutValue("重物/泡货类型");
cells[0, 8].SetStyle(style2);
cells.SetColumnWidth(8, 20);
cells[0, 9].PutValue("包装要求");
cells[0, 9].SetStyle(style2);
cells.SetColumnWidth(9, 20);
cells[0, 10].PutValue("发车时间");
cells[0, 10].SetStyle(style2);
cells.SetColumnWidth(10, 20);
cells[0, 11].PutValue("审核装态");
cells[0, 11].SetStyle(style2);
cells.SetColumnWidth(11, 20);
cells[0, 12].PutValue("审核时间");
cells[0, 12].SetStyle(style2);
cells.SetColumnWidth(12, 20);
string where = "";
if (!string.IsNullOrEmpty(userxm))
{
where += " and " + dbc.C_Like("a.SaleRecordUserXM", userxm, LikeStyle.LeftAndRightLike);
}
if (!string.IsNullOrEmpty(isVerifyType))
{
where += " and " + dbc.C_EQ("a.SaleRecordVerifyType", Convert.ToInt32(isVerifyType));
}
if (!string.IsNullOrEmpty(cx_beg))
{
where += " and a.SaleRecordTime >= " + dbc.ToSqlValue(Convert.ToDateTime(cx_beg));
}
if (!string.IsNullOrEmpty(cx_end))
{
where += " and a.SaleRecordTime < " + dbc.ToSqlValue(Convert.ToDateTime(cx_end).AddDays(1));
}
string str = @"select a.*,b.ZXSaleListCitys,c.ZdMxMc as ProduceLx, d.ZdMxMc as PackLx,e.ZdMxMc as Fc,f.ZdMxMc as ZhLx,g.ZdMxMc as PhLx,h.FromRoute,h.ToRoute
from tb_b_salerecord a left join tb_b_zxsalelist b on a.SaleRecordID=b.SaleRecordID
left join tb_b_zdmx c on b.ZXSaleListProduceLx=c.ZdMxID
left join tb_b_zdmx d on b.ZXSaleListPackLx=d.ZdMxID
left join tb_b_zdmx e on b.ZXSaleListFc=e.ZdMxID
left join tb_b_zdmx f on b.ZXSaleListZhLx=f.ZdMxID
left join tb_b_zdmx g on b.ZXSaleListPhLx=g.ZdMxID
left join tb_b_user h on a.SaleRecordUserID=h.UserID
where a.status=0 and a.SaleRecordLX!=0 and a.SaleRecordVerifyType!=3
and b.status=0 " + where + @" order by a.addtime desc";
System.Data.DataTable dt = dbc.ExecuteDataTable(str);
for (int i = 0; i < dt.Rows.Count; i++)
{
cells[i + 1, 0].PutValue(dt.Rows[i]["SaleRecordUserXM"]);
cells[i + 1, 0].SetStyle(style4);
var xl = "";
if (dt.Rows[i]["FromRoute"] != null && dt.Rows[i]["FromRoute"].ToString() != "")
{
xl += dt.Rows[i]["FromRoute"].ToString();
}
if (dt.Rows[i]["ToRoute"] != null && dt.Rows[i]["ToRoute"].ToString() != "")
{
xl += "─" + dt.Rows[i]["ToRoute"].ToString();
}
cells[i + 1, 1].PutValue(xl);
cells[i + 1, 1].SetStyle(style4);
cells[i + 1, 2].PutValue(dt.Rows[i]["ZXSaleListCitys"]);
cells[i + 1, 2].SetStyle(style4);
if (dt.Rows[i]["SaleRecordPoints"] != null && dt.Rows[i]["SaleRecordPoints"].ToString() != "")
{
cells[i + 1, 3].PutValue(dt.Rows[i]["SaleRecordPoints"]);
}
cells[i + 1, 3].SetStyle(style4);
if (dt.Rows[i]["SaleRecordDiscount"] != null && dt.Rows[i]["SaleRecordDiscount"].ToString() != "")
{
cells[i + 1, 4].PutValue(dt.Rows[i]["SaleRecordDiscount"]);
}
cells[i + 1, 4].SetStyle(style4);
if (dt.Rows[i]["ValidHour"] != null && dt.Rows[i]["ValidHour"].ToString() != "")
{
cells[i + 1, 5].PutValue(dt.Rows[i]["ValidHour"]);
}
cells[i + 1, 5].SetStyle(style4);
if (dt.Rows[i]["SaleRecordTime"] != null && dt.Rows[i]["SaleRecordTime"].ToString() != "")
{
cells[i + 1, 6].PutValue(Convert.ToDateTime(dt.Rows[i]["SaleRecordTime"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
cells[i + 1, 6].SetStyle(style4);
if (dt.Rows[i]["ProduceLx"] != null && dt.Rows[i]["ProduceLx"].ToString() != "")
{
cells[i + 1, 7].PutValue(dt.Rows[i]["ProduceLx"]);
}
cells[i + 1, 7].SetStyle(style4);
var lx = "";
if (dt.Rows[i]["ProduceLx"] != null && dt.Rows[i]["ProduceLx"].ToString() != "")
{
if (dt.Rows[i]["ProduceLx"].ToString() == "重货")
{
if (dt.Rows[i]["ZhLx"] != null && dt.Rows[i]["ZhLx"].ToString() != "")
{
lx = dt.Rows[i]["ZhLx"].ToString();
}
}
else if (dt.Rows[i]["ProduceLx"].ToString() == "泡货")
{
if (dt.Rows[i]["PhLx"] != null && dt.Rows[i]["PhLx"].ToString() != "")
{
lx = dt.Rows[i]["PhLx"].ToString();
}
}
}
cells[i + 1, 8].PutValue(lx);
cells[i + 1, 8].SetStyle(style4);
if (dt.Rows[i]["PackLx"] != null && dt.Rows[i]["PackLx"].ToString() != "")
{
cells[i + 1, 9].PutValue(dt.Rows[i]["PackLx"]);
}
cells[i + 1, 9].SetStyle(style4);
if (dt.Rows[i]["Fc"] != null && dt.Rows[i]["Fc"].ToString() != "")
{
cells[i + 1, 10].PutValue(dt.Rows[i]["Fc"]);
}
cells[i + 1, 10].SetStyle(style4);
var shzt = "";
if (dt.Rows[i]["SaleRecordVerifyType"] != null && dt.Rows[i]["SaleRecordVerifyType"].ToString() != "")
{
if (Convert.ToInt32(dt.Rows[i]["SaleRecordVerifyType"].ToString()) == 1)
{
shzt = "通过";
}
else if (Convert.ToInt32(dt.Rows[i]["SaleRecordVerifyType"].ToString()) == 2)
{
shzt = "拒绝";
}
else if (Convert.ToInt32(dt.Rows[i]["SaleRecordVerifyType"].ToString()) == 0)
{
shzt = "待审核";
}
}
else
{
shzt = "待审核";
}
cells[i + 1, 11].PutValue(shzt);
cells[i + 1, 11].SetStyle(style4);
if (dt.Rows[i]["SaleRecordVerifyTime"] != null && dt.Rows[i]["SaleRecordVerifyTime"].ToString() != "")
{
cells[i + 1, 12].PutValue(Convert.ToDateTime(dt.Rows[i]["SaleRecordVerifyTime"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
cells[i + 1, 12].SetStyle(style4);
}
MemoryStream ms = workbook.SaveToStream();
byte[] bt = ms.ToArray();
return(bt);
}
catch (Exception ex)
{
throw ex;
}
}
}
public override void RunCommand(object sender)
{
var engine = (IAutomationEngineInstance)sender;
var vKeyColumn = v_KeyColumn.ConvertUserVariableToString(engine);
var vValueColumn = v_ValueColumn.ConvertUserVariableToString(engine);
var excelObject = v_InstanceName.GetAppInstance(engine);
var excelInstance = (Application)excelObject;
Worksheet excelSheet = excelInstance.ActiveSheet;
Range sourceRange = excelSheet.UsedRange;
var last = excelInstance.GetAddressOfLastCell(excelSheet);
Range cellValue = excelSheet.Range["A1", last];
int rw = cellValue.Rows.Count;
int cl = 2;
int rCnt;
int cCnt;
DataTable DT = new DataTable();
for (rCnt = 2; rCnt <= rw; rCnt++)
{
DataRow newRow = DT.NewRow();
for (cCnt = 1; cCnt <= cl; cCnt++)
{
if (((cellValue.Cells[rCnt, cCnt] as Range).Value2) != null)
{
if (!DT.Columns.Contains(cCnt.ToString()))
{
DT.Columns.Add(cCnt.ToString());
}
newRow[cCnt.ToString()] = ((cellValue.Cells[rCnt, cCnt] as Range).Value2).ToString();
}
}
DT.Rows.Add(newRow);
}
string cKeyName = ((cellValue.Cells[1, 1] as Range).Value2).ToString();
DT.Columns[0].ColumnName = cKeyName;
string cValueName = ((cellValue.Cells[1, 2] as Range).Value2).ToString();
DT.Columns[1].ColumnName = cValueName;
var dictlist = DT.AsEnumerable().Select(x => new
{
keys = (string)x[vKeyColumn],
values = (string)x[vValueColumn]
}).ToList();
Dictionary <string, string> outputDictionary = new Dictionary <string, string>();
foreach (var dict in dictlist)
{
outputDictionary.Add(dict.keys, dict.values);
}
outputDictionary.StoreInUserVariable(engine, v_OutputUserVariableName, nameof(v_OutputUserVariableName), this);
}
private void CopyRange(ref CellRangeTemplate sourceRange, SheetData sheetData, Worksheet worksheet, ref CellPosition target, double?rowHeight = null)
{
#region
Sheet sheetTemplate = sourceRange.CellRange.SheetTemplate;
var workbookPartTemplate = sourceRange.CellRange.WorksheetPart;
MergeCells mergeCellsTemplate = sourceRange.CellRange.MergeCells;
MergeCells mergeCells = worksheet.GetFirstChild <MergeCells>();
if (false && workbookPartTemplate.DrawingsPart != null && worksheet.WorksheetPart.DrawingsPart == null)
{
var drawingsPart = worksheet.WorksheetPart.AddPart <DrawingsPart>(workbookPartTemplate.DrawingsPart);
drawingsPart = worksheet.WorksheetPart.DrawingsPart;
if (!worksheet.WorksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any())
{
worksheet.WorksheetPart.Worksheet.Append(new Drawing {
Id = worksheet.WorksheetPart.GetIdOfPart(drawingsPart)
});
}
}
Dictionary <string, MergeCell> mergeCellTemplateDic = sourceRange.CellRange.MergeCellsDic;
#endregion
CellPosition source = sourceRange.CellRange.Start;
CellPosition offset = target.CalculateOffset(source);
var cellsToCopy = document.FindCellsByRange(sourceRange.CellRange);
for (int i = 0; i < cellsToCopy.Count(); i++)
{
var rowGroup = cellsToCopy.ElementAt(i);
Row keyRow = rowGroup.Key;
Row targetRow = new Row()
{
RowIndex = (UInt32)(keyRow.RowIndex + offset.Row),
Height = (short)-1,
};
if (rowHeight != null)
{
targetRow.Height = rowHeight;
targetRow.CustomHeight = true;
}
MoveCurrentRow((int)targetRow.RowIndex.Value);
sheetData.InsertBefore(targetRow, currentRow);
foreach (Cell cellToCopy in rowGroup)
{
Cell targetCell = (Cell)cellToCopy.Clone();
targetCell.CellReference = CellPosition.OffsetIt(targetCell.CellReference, offset);
targetRow.Append(targetCell);
MergeCell _findMerge;
if (mergeCellTemplateDic != null && mergeCellTemplateDic.TryGetValue(cellToCopy.CellReference.Value.ToUpper(), out _findMerge))
{
var positionParent = _findMerge.Reference.Value.Split(':');
CellPosition offsetStart = new CellPosition(positionParent[0]);
CellPosition offsetEnd = new CellPosition(positionParent[1]);
var celRefNew = new CellPosition(targetCell.CellReference);
if (mergeCells == null)
{
var a = new MergeCells();
worksheet.InsertAfter(a, sheetData);
mergeCells = worksheet.GetFirstChild <MergeCells>();
}
var mergeCell = new MergeCell();
mergeCell.Reference = celRefNew.ToString() + ":" + new CellPosition(celRefNew.Row + (offsetEnd.Row - offsetStart.Row), celRefNew.Column + (offsetEnd.Column - offsetStart.Column)).ToString();
mergeCells.Append(mergeCell);
mergeCells.Count = (mergeCells.Count ?? 0) + 1;
}
}
}
}
/// <summary>
/// This method will return the total amount of columns of a given excel file that are not null
/// </summary>
/// <param name="pathToExcel"></param>
/// <returns>Integer with the total amount of columns</returns>
public static int GetTotalAmountOfColumns(string pathToExcel)
{
//Se crea una instancia de una aplicación de Excel
Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
//False para que no abra la aplicación, sino que lo haga "por atrás"
myExcel.Visible = false;
//Aquí usando la instancia de Aplicación de excel, abro el libro mandando como parámetro la ruta a mi archivo
Microsoft.Office.Interop.Excel.Workbook workbook = myExcel.Workbooks.Open(pathToExcel);
//Después uso una instancia de Worksheet (clase de Interop) para obtener la Hoja actual del archivo Excel
Worksheet worksheet = myExcel.ActiveSheet;
//En ese worksheet, en la propiedad de Name, tenemos el nombre de la hoja actual, que mando en el query 1 como parámetro
//Console.WriteLine("WorkSheet.Name: " + worksheet.Name);
string hojaExcel = worksheet.Name;
bool errorDetected = false;
int initialColumn = 1;
int totalColumns = 0;
while (!errorDetected)
{
try
{
if (worksheet.Cells[1, initialColumn].Value2 != null)
{
//initialRow++;
totalColumns++;
initialColumn++;
//Console.WriteLine(worksheet.Cells[1, initialColumn].Value2 + " " + initialColumn);
}
else
{
break;
}
}
catch (Exception e)
{
errorDetected = true;
}
}
//Al finalizar tu proceso debes cerrar tu workbook
workbook.Close();
myExcel.Quit();
//Con esto de Marshal se libera de manera completa el objeto desde Interop Services, si no haces esto
//El objeto sigue en memoria, no lo libera C#
Marshal.FinalReleaseComObject(worksheet);
Marshal.FinalReleaseComObject(workbook);
Marshal.FinalReleaseComObject(myExcel);
GC.Collect();
GC.WaitForPendingFinalizers();
return(totalColumns);
}
private void TransferExcelDataToDataBase(string SheetPath, string Sheetname, DataSet MisDataset)
{
// OleDbConnection ole = null;
// OleDbDataAdapter da = null;
// DataTable dt = null;
// string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';", Sheetname.Trim());
// if ((System.IO.Path.GetExtension(Sheetname.Trim())).ToLower() == ".xls")
// {
// strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + Sheetname.Trim() + ";Extended Properties=Excel 5.0;Persist Security Info=False";
// }
//// string sTableName = "1234";
// string strExcel = "select * from [1234$]";
// try
// {
// ole = new OleDbConnection(strConn);
// ole.Open();
// DataTable OleDataTable = ole.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// string SheetName = OleDataTable.Rows[0][2].ToString().Trim();
// strExcel = String.Format("select * from[" + SheetName + "]");
// da = new OleDbDataAdapter(strExcel, ole);
// dt = new DataTable();
// da.Fill(dt);
// this.DataViewSheetview.DataSource = dt;
// //因为生成Excel的时候第一行是标题，所以要做如下操作：
// //1.修改DataGridView列头的名字，
// //2.数据列表中删除第一行
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// DataViewSheetview.Columns[i].HeaderCell.Value = dt.Rows[0][i].ToString();//c# winform 用代码修改DataGridView列头的名字，设置列名,修改列名
// }
// //DataGridView删除行
// DataViewSheetview.Rows.Remove(DataViewSheetview.Rows[0]);//删除第一行
// ole.Close();
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message);
// }
// finally
// {
// if (ole != null)
// ole.Close();
// }
// DataSet ds = new DataSet();
Workbook workBook = new Workbook(SheetPath);
//string SheetName = workBook.Worksheets[0].Name;
//Worksheet workSheet = workBook.Worksheets[Sheetname];
Worksheet workSheet = workBook.Worksheets[0];
Cells cell = workSheet.Cells;
DataTable dt = new DataTable();
int count = cell.Columns.Count;
for (int i = 0; i < count; i++)
{
string str = cell.GetRow(0)[i].StringValue;
dt.Columns.Add(new DataColumn(str));
}
for (int i = 1; i < cell.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < count; j++)
{
dr[j] = cell[i, j].StringValue;
}
dt.Rows.Add(dr);
}
dt.AcceptChanges();
this.DataViewSheetview.DataSource = dt;
MisDataset.Tables.Add(dt);
// return ds;
}
private static Record EncodePictures(Dictionary <Pair <int, int>, Picture> pictures, SharedResource sharedResource, Worksheet worksheet)
{
MSODRAWING msoDrawing = new MSODRAWING();
MsofbtDgContainer dgContainer = new MsofbtDgContainer();
msoDrawing.EscherRecords.Add(dgContainer);
MsofbtDg dg = new MsofbtDg();
dg.Instance = 1;
dg.NumShapes = pictures.Count + 1;
dg.LastShapeID = 1024 + pictures.Count;
dgContainer.EscherRecords.Add(dg);
MsofbtSpgrContainer spgrContainer = new MsofbtSpgrContainer();
dgContainer.EscherRecords.Add(spgrContainer);
MsofbtSpContainer spContainer0 = new MsofbtSpContainer();
spContainer0.EscherRecords.Add(new MsofbtSpgr());
MsofbtSp shape0 = new MsofbtSp();
shape0.ShapeId = 1024;
shape0.Flags = ShapeFlag.Group | ShapeFlag.Patriarch;
shape0.Version = 2;
spContainer0.EscherRecords.Add(shape0);
spgrContainer.EscherRecords.Add(spContainer0);
foreach (Picture pic in pictures.Values)
{
if (!sharedResource.Images.Contains(pic.Image))
{
sharedResource.Images.Add(pic.Image);
}
MsofbtSpContainer spContainer = new MsofbtSpContainer();
MsofbtSp shape = new MsofbtSp();
shape.Version = 2;
shape.ShapeType = ShapeType.PictureFrame;
shape.ShapeId = 1024 + spgrContainer.EscherRecords.Count;
shape.Flags = ShapeFlag.Haveanchor | ShapeFlag.Hasshapetype;
spContainer.EscherRecords.Add(shape);
MsofbtOPT opt = new MsofbtOPT();
opt.Add(PropertyIDs.LockAgainstGrouping, 33226880);
opt.Add(PropertyIDs.FitTextToShape, 262148);
opt.Add(PropertyIDs.BlipId, (uint)sharedResource.Images.IndexOf(pic.Image) + 1);
spContainer.EscherRecords.Add(opt);
MsofbtClientAnchor anchor = new MsofbtClientAnchor();
anchor.Row1 = pic.TopLeftCorner.RowIndex;
anchor.Col1 = pic.TopLeftCorner.ColIndex;
anchor.DX1 = pic.TopLeftCorner.DX;
anchor.DY1 = pic.TopLeftCorner.DY;
anchor.Row2 = pic.BottomRightCorner.RowIndex;
anchor.Col2 = pic.BottomRightCorner.ColIndex;
anchor.DX2 = pic.BottomRightCorner.DX;
anchor.DY2 = pic.BottomRightCorner.DY;
anchor.ExtraData = new byte[0];
spContainer.EscherRecords.Add(anchor);
spContainer.EscherRecords.Add(new MsofbtClientData());
spgrContainer.EscherRecords.Add(spContainer);
}
return(msoDrawing);
}
public static Workbook CreateExcel(ExcelFormat FormatType, bool Save = true)
{
//实例化workbook对象
Workbook workbook = new Workbook();
//获得工作簿中的表
Worksheet sheet = (Worksheet)workbook.Worksheets[FormatType.SheetIndex];
//获得指定Sheet中的所有单元格
Cells cells = sheet.Cells;
//有行标题
if (FormatType.Columns.Count > 0)
{
//设置第一行行高
cells.SetRowHeight(0, FormatType.RowsSize);
for (int i = 0; i < FormatType.Columns.Count; i++)
{
//设置列宽
cells.SetColumnWidth(i, FormatType.ColumnsSize);
//单元格赋值
cells[0, FormatType.Columns[i].Y].PutValue(FormatType.Columns[i].Txt_Obj);
//设置样式
cells[0, FormatType.Columns[i].Y].SetStyle(FormatType.Columns[i].CreateStyle());
}
}
//有行标题
if (FormatType.Rows.Count > 0)
{
//设置第一列列宽
cells.SetColumnWidth(0, FormatType.ColumnsSize);
for (int i = 0; i < FormatType.Rows.Count; i++)
{
//设置行高
cells.SetRowHeight(i, FormatType.RowsSize);
//单元格赋值
cells[FormatType.Rows[i].X, 0].PutValue(FormatType.Rows[i].Txt_Obj);
//设置样式
cells[FormatType.Rows[i].X, 0].SetStyle(FormatType.Rows[i].CreateStyle());
//cells[i, 0].SetStyle(FormatType.Rows[i].CreateStyle());
}
}
if (FormatType.SCells.Count > 0)
{
foreach (var cell in FormatType.SCells)
{
//设置行高
cells.SetRowHeight(cell.X, FormatType.RowsSize);
//设置列宽
cells.SetColumnWidth(cell.Y, FormatType.ColumnsSize);
//设置文字换行
//单元格赋值
cells[cell.X, cell.Y].PutValue(cell.Txt_Obj);
//设置样式
cells[cell.X, cell.Y].SetStyle(cell.CreateStyle());
}
}
if (Save)
{
try
{
workbook.Save(FormatType.SavePath);
}
catch (Exception ex)
{
Console.WriteLine("保存失败");
throw ex;
}
}
return(workbook);
}
//private ABIE_Paragraph xlTopMargin;
//private ABIE_Paragraph xlLeftMargin;
//private ABIE_Paragraph xlBotMargin;
//private ABIE_Paragraph xlRightMargin;
//private ABIE_Paragraph xlLeftHeader;
//private ABIE_Paragraph xlCenterHeader;
//private ABIE_Paragraph xlRightHeader;
//private ABIE_Paragraph xlLeftFooter;
//private ABIE_Paragraph xlCenterFooter;
//private ABIE_Paragraph xlRightFooter;
public ABIE_WorkSheet(Worksheet worksheet)
{
this.Worksheet = worksheet;
}
private void DataSetTableToExcel(DataSet dataset)
{
DataTable Table = dataset.Tables[0];
//int RowNumber = Table.Rows.Count;
//int ColumnNumber = Table.Columns.Count;
//if (RowNumber == 0)
//{
// MessageBox.Show("没有任何数据可以打印！");
//}
//Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
//excel.Visible = true;
//for (int i = 0; i < ColumnNumber; i++)
//{
// excel.Cells[1, i + 1] = dataset.Tables[0].Columns[i].ColumnName;
//}
//for (int c = 0; c < RowNumber; c++)
//{
// for (int j = 0; j < ColumnNumber; j++)
// {
// excel.Cells[c + 2, j + 1] = Table.Rows[c].ItemArray[j];
// }
//}
//string json = value.Value;
//DataTable dt = Utils.JsonDataTableConvert.ToDataTable(json);
//string fileName = tableTemplate.Name + ".xls";
//string savePath = Server.MapPath("~/Upload/TempExcelDownLoad/" + fileName);
string FilePath = "";
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "excel表格|*.xlsx|excel表格|*.xls|所有文件|*.*";
if (sfd.ShowDialog() == DialogResult.OK)
{
FilePath = System.IO.Path.GetFullPath(sfd.FileName);
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
int Colnum = Table.Columns.Count; //表格列数
int Rownum = Table.Rows.Count; //表格行数
//生成行 列名行
for (int i = 0; i < Colnum; i++)
{
cells[0, i].PutValue(Table.Columns[i].ColumnName);
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[1 + i, k].PutValue(Table.Rows[i][k].ToString());
}
}
book.Save(FilePath);
}
}
private bool ManipulateExcel()
{
try {
//檢查查詢日期格式是否正確
if (txtMonth.Text.SubStr(5, 2) == "00")
{
MessageDisplay.Error("月份輸入錯誤!");
return(false);
}
txtMonth.Enabled = false;
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
lblProcessing.Visible = true;
ShowMsg("開始轉檔...");
string rptName, rptId, file;
int f;
rptName = "STF報價每月獎勵活動成績得獎名單月報表";
rptId = "50070";
ShowMsg(rptId + "－" + rptName + " 轉檔中...");
//讀取資料
daoRMM = new R_MARKET_MONTHLY();
string asYM = txtMonth.Text.Replace("/", "");
DataTable dt50070 = daoRMM.ListAllByDate(asYM);
if (dt50070.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", asYM, rptName));
return(false);
}
//複製檔案
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(false);
}
//切換Sheet
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
Worksheet ws50070 = workbook.Worksheets[0];
//填資料 Sheet1
ws50070.Import(dt50070, false, 2, 0);
//讀取資料
dt50070 = daoRMM.ListAll2ByDate(asYM);
if (dt50070.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", asYM, rptName));
return(false);
}
//切換Sheet
ws50070 = workbook.Worksheets[1];
//填資料 Sheet2
ws50070.Import(dt50070, false, 0, 0);
//存檔
workbook.SaveDocument(file);
ShowMsg("轉檔成功");
return(true);
}
catch (Exception ex) {
MessageDisplay.Error("輸出錯誤");
throw ex;
}
finally {
this.Cursor = Cursors.Arrow;
this.Refresh();
Thread.Sleep(5);
txtMonth.Enabled = true;
}
}
public static List <Record> Encode(Worksheet worksheet, SharedResource sharedResource)
{
List <Record> records = new List <Record>();
BOF bof = new BOF();
bof.BIFFversion = 0x0600; //0600H = BIFF8
bof.StreamType = StreamType.Worksheet;
bof.BuildID = 3515;
bof.BuildYear = 1996;
bof.RequiredExcelVersion = 6;
records.Add(bof);
foreach (KeyValuePair <Pair <UInt16, UInt16>, UInt16> colWidth in worksheet.Cells.ColumnWidth)
{
COLINFO colInfo = new COLINFO();
colInfo.FirstColIndex = colWidth.Key.Left;
colInfo.LastColIndex = colWidth.Key.Right;
colInfo.Width = colWidth.Value;
records.Add(colInfo);
}
DIMENSIONS dimensions = new DIMENSIONS();
if (worksheet.Cells.Rows.Count > 0)
{
dimensions.FirstRow = worksheet.Cells.FirstRowIndex;
dimensions.FirstColumn = (Int16)worksheet.Cells.FirstColIndex;
dimensions.LastRow = worksheet.Cells.LastRowIndex + 1;
dimensions.LastColumn = (Int16)(worksheet.Cells.LastColIndex + 1);
}
records.Add(dimensions);
// each Row Block contains 32 consecutive rows
List <Record> rowblock = new List <Record>(32);
List <Record> cellblock = new List <Record>();
for (int rowIndex = dimensions.FirstRow; rowIndex < dimensions.LastRow; rowIndex++)
{
if (worksheet.Cells.Rows.ContainsKey(rowIndex))
{
Row sheetRow = worksheet.Cells.Rows[rowIndex];
ROW biffRow = new ROW();
biffRow.RowIndex = (UInt16)rowIndex;
biffRow.FirstColIndex = (UInt16)sheetRow.FirstColIndex;
biffRow.LastColIndex = (UInt16)(sheetRow.LastColIndex + 1);
biffRow.RowHeight = sheetRow.Height;
biffRow.Flags = 0x0F0100; // defaul value 0x0100
rowblock.Add(biffRow);
for (int colIndex = sheetRow.FirstColIndex; colIndex <= sheetRow.LastColIndex; colIndex++)
{
Cell cell = sheetRow.GetCell(colIndex);
if (cell != Cell.EmptyCell && cell.Value != null)
{
CellValue cellRecord = EncodeCell(cell, sharedResource);
cellRecord.RowIndex = (UInt16)rowIndex;
cellRecord.ColIndex = (UInt16)colIndex;
cellRecord.XFIndex = (UInt16)sharedResource.GetXFIndex(cell.Format);
cellblock.Add(cellRecord);
}
}
if (rowblock.Count == 32)
{
records.AddRange(rowblock);
records.AddRange(cellblock);
rowblock.Clear();
cellblock.Clear();
}
}
}
if (rowblock.Count > 0)
{
records.AddRange(rowblock);
records.AddRange(cellblock);
}
if (worksheet.Pictures.Count > 0)
{
records.Add(EncodePictures(worksheet.Pictures, sharedResource, worksheet));
for (ushort id = 1; id <= worksheet.Pictures.Count; id++)
{
OBJ obj = new OBJ();
CommonObjectData objData = new CommonObjectData();
objData.ObjectID = id;
objData.ObjectType = 8;
objData.OptionFlags = 24593;
obj.SubRecords.Add(objData);
obj.SubRecords.Add(new End());
records.Add(obj);
}
}
EOF eof = new EOF();
records.Add(eof);
return(records);
}
